Cleaning Messy Data With T-SQL

A source file looks tidy until a date column contains a word. Cleaning messy data with T-SQL works best when the raw row survives every attempted correction.

Keep the Raw Landing Zone When Cleaning Messy Data

Load source fields as received into a landing table, with file or request ID, row number, arrival time, and raw values. Do not immediately overwrite them with cleaned values. The original row is evidence when a source owner asks why a record was rejected.

I separate physical parsing from business validation. Can the file be read into fields? Then, are those fields valid for the target? A malformed quote or unexpected column count is a file problem. An invalid order date is a row problem. Different problems need different correction paths.

The landing zone also makes replay possible. If a rule changes, you can reprocess the original rows without requesting the file again. Set retention based on support and audit needs. A temporary table that vanishes at the first failure is not enough for a dependable load.

Trim and Normalize Text Deliberately

TRIM removes surrounding spaces from strings. NULLIF can turn an empty result into NULL when blank and missing have the same business meaning. Use the target column’s collation and length requirements deliberately. Do not assume every field should be uppercased or stripped of punctuation.

Names and addresses can contain meaningful spaces and punctuation. Normalize comparison keys separately from display values. For coded fields, a reference table can map known aliases to canonical codes. Keep the original code for diagnosis, particularly when a new alias appears.

I have seen a cleaning rule remove characters so aggressively that two different keys became identical. A tidy value is not automatically a correct value. Check uniqueness after normalization and reject collisions for review rather than choosing a winner silently.

SELECT SourceRowId, RawCustomerCode,
       NULLIF(TRIM(RawCustomerCode), N'') AS CleanCustomerCode
FROM dbo.RawCustomer;

Parse Dates With a Known Format

TRY_CONVERT returns NULL when text cannot be converted to the requested type. Use a conversion style that matches the source contract. For ISO date text, style 23 is explicit. Check blank source values separately so an empty optional date is not confused with a malformed nonblank date.

Avoid relying on session language or date format. A string such as 04/05/2025 is ambiguous. The source contract should say what it means before the database tries to parse it. If the provider cannot give a stable format, include the format identifier with the feed or reject ambiguous values.

I want the reject report to show the raw date, source row number, and rule name. “Conversion failed” is not enough when a file contains hundreds of rows. The exact text tells the producer what to correct.

SELECT SourceRowId, RawOrderDate
FROM dbo.RawOrder
WHERE NULLIF(TRIM(RawOrderDate), N'') IS NOT NULL
  AND TRY_CONVERT(date, RawOrderDate, 23) IS NULL;

Validate Numbers and Required Keys

Amounts can arrive with currency symbols, commas, or spaces. Do not remove characters blindly. Decide which formats are allowed, normalize only those, and use TRY_CONVERT on the result. A value that parses but violates a business rule, such as a negative amount, needs a different rejection reason.

Check required business keys before loading the target. A blank key makes replay and updates unsafe. Check duplicate keys within the current delivery, then compare with target uniqueness rules. A source row ID is useful for diagnosis but does not replace a business key.

What should happen when a field exceeds target length? Truncation can hide distinct values. Reject the row or apply an agreed transformation, then record it. I prefer a loud reject over a clean target row that silently lost the part that mattered.

SELECT SourceRowId, RawAmount
FROM dbo.RawOrder
WHERE NULLIF(TRIM(RawAmount), N'') IS NOT NULL
  AND TRY_CONVERT(decimal(18,2), RawAmount) IS NULL;

Record Rejections for Review

Store RunId, source row ID, rule code, offending value, and detection time in a reject table. One row can violate several rules, so decide whether to keep one record per violation or a structured list. A clear rule code is easier to group and trend.

Do not delete rejected rows after sending an alert. The source owner needs enough evidence to fix the feed, and the operator needs a way to replay corrected rows. Keep the raw payload under appropriate access control. Reject tables can contain the same sensitive information as the target.

I count accepted, rejected, and intentionally filtered rows. Their sum should reconcile to the landing count. If it does not, the process has lost track of input. That is a data quality failure even if the target INSERT returned success.

Publish Only Valid Rows After Cleaning Messy Data

Build a clean staging set with typed columns after validation. Apply it to the target in a controlled transaction. If a severe rule fails, leave the prior published data available and mark the run failed. If partial acceptance is permitted, state that policy explicitly and record the rejected count.

A unique constraint on the target remains the last safety net. The stage checks provide a complete problem list before the transaction. Both layers matter. The first explains the failure; the second prevents invalid data from slipping through if a check is missed.

Use a stable run identifier through landing, rejection, and target apply. A retry should recognize previously accepted rows. I test a replay before calling the pipeline finished. The second pass should not double the business facts just because the file appeared twice.

Improve the Cleaning Rule for Messy Data, Not Just the File

Trend rejection reasons over deliveries. Repeated bad dates can point to an unclear source format contract. New unexpected codes can mean a legitimate business change rather than a bad row. Review the rule with the source owner before quietly expanding it.

Keep cleaning logic in named views or procedures instead of scattering REPLACE calls across reports. A report should consume typed, accepted data. It should not decide whether a string is a date every time someone opens a dashboard. That repeated guess creates inconsistent totals.

Cleaning messy data is a controlled translation from source values to trusted values. Preserve the original, explain each transformation, and keep every reject available for review. Then a bad input has a path to correction instead of a path to silent disappearance.

Related reading on this blog: "Clean Data" Is Not a Requirement: Writing Rules People Can Act On and Troubleshooting Common CSV Import Issues.

Clean data is not data with every odd value removed, it is data with every change explained.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.