Cleaning Address Data in T-SQL: Trimming, Casing and Standardizing

Three spellings of the same street can make one customer look like three customers. Start cleaning address data with repeatable rules and retained originals, so every correction remains explainable.

Preserve What the Person Entered

Separate the raw address from the cleaned address. Keep a source identifier, country, and original postal text. Write the derived values into new columns. That allows review, rule changes, and a fresh cleaning pass without pretending the original input was better than it was.

I preserve the raw values before touching casing or punctuation. An abbreviation can be a street suffix, a person's name, or part of a building name. Aggressive replacement removes clues. A perfectly neat wrong address is still going to the wrong place.

Use a scratch database and run these examples in one SSMS session. The temporary table uses illustrative American address strings. They are teaching inputs, not verified delivery locations. The code uses the ordinal option of STRING_SPLIT from SQL Server 2022 and runs on SQL Server 2025.

CREATE TABLE #AddressWork
(
    AddressID int NOT NULL PRIMARY KEY,
    CountryCode char(2) NOT NULL,
    OriginalAddress nvarchar(200) NULL,
    OriginalPostal nvarchar(32) NULL,
    CleanAddress nvarchar(200) NULL,
    CleanPostal nvarchar(32) NULL,
    NeedsReview bit NOT NULL DEFAULT 0
);
INSERT #AddressWork
    (AddressID, CountryCode, OriginalAddress, OriginalPostal)
VALUES
(1, 'US', N'  120   MAIN Street  ', N' 02110 '),
(2, 'US', N'500 oak ST. apt 3B', N'10001 1234'),
(3, 'US', N'77 pine Str.', N'ABCDE'),
(4, 'US', NULL, N'');

Cleaning Address Data Starts With Spaces

TRIM handles ordinary leading and trailing spaces. Tabs, line breaks, and nonbreaking spaces need explicit handling. The first update converts those selected characters to regular spaces, then trims. It always starts from the original columns, making the rule reproducible.

Repeated REPLACE calls collapse double spaces until none remain. A single replacement pass does not reliably remove every run of several spaces. The loop is simple for this teaching table. For a large load, process bounded batches and measure the work rather than launching a row-by-row repair during a busy period.

Keep NULL distinct from a blank entered value when that distinction matters. Later review logic can classify both as missing. Normalizing whitespace does not justify deleting meaningful punctuation or combining separate address lines without a defined rule.

UPDATE #AddressWork
SET CleanAddress = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(
        OriginalAddress, NCHAR(9), N' '), NCHAR(10), N' '),
        NCHAR(13), N' '), NCHAR(160), N' ')),
    CleanPostal = UPPER(TRIM(OriginalPostal)),
    NeedsReview = 0;
WHILE EXISTS (SELECT 1 FROM #AddressWork WHERE CHARINDEX(N'  ', CleanAddress) > 0)
BEGIN
    UPDATE #AddressWork
    SET CleanAddress = REPLACE(CleanAddress, N'  ', N' ')
    WHERE CHARINDEX(N'  ', CleanAddress) > 0;
END;
SELECT AddressID, OriginalAddress, CleanAddress FROM #AddressWork ORDER BY AddressID;

Use a Small Casing Rule With Known Limits

The following function uppercases the first character of each space-separated word and lowercases the rest. It is a display rule, not a universal rule for names. Apostrophes, hyphens, initials, and language-specific casing need their own policies and tests.

Create this helper only in the scratch database. CREATE FUNCTION begins its own batch, and GO ends it before any later statement. The example has a fixed input width matching the address column. Preserve Unicode with nvarchar rather than throwing away characters during normalization.

CREATE FUNCTION dbo.AddressWordCase (@Input nvarchar(200))
RETURNS nvarchar(200)
AS
BEGIN
    IF @Input IS NULL RETURN NULL;
    DECLARE @Lower nvarchar(200) = LOWER(@Input);
    DECLARE @Result nvarchar(200) = N'';
    DECLARE @Position int = 1;
    DECLARE @Capitalize bit = 1;
    DECLARE @Character nchar(1);
    WHILE @Position <= LEN(@Lower)
    BEGIN
        SET @Character = SUBSTRING(@Lower, @Position, 1);
        SET @Result = @Result +
            CASE WHEN @Capitalize = 1 THEN UPPER(@Character) ELSE @Character END;
        SET @Capitalize = CASE WHEN @Character = N' ' THEN 1 ELSE 0 END;
        SET @Position += 1;
    END;
    RETURN @Result;
END;
GO

Map Street Words When Cleaning Address Data

A small lookup table makes approved abbreviations visible. Map STREET, ST, and STR to one chosen display value. Remove periods only from the lookup comparison, not from every token in the address. Whole-token mapping avoids rewriting letters buried inside a larger word.

The next block preserves token order through ordinal. STRING_SPLIT does not guarantee output order by itself. STRING_AGG therefore orders the rebuilt address explicitly. Digit-containing tokens retain their entered casing so an apartment identifier such as 3B does not become 3b.

This example maps approved tokens anywhere in the line for clarity. A production parser should restrict suffix replacement to an identified street-suffix position. A word in a building name can match the same lookup. Flag that ambiguity instead of declaring the cleaned value authoritative.

CREATE TABLE #StreetWords
(
    MatchWord nvarchar(20) NOT NULL PRIMARY KEY,
    DisplayWord nvarchar(20) NOT NULL
);
INSERT #StreetWords VALUES (N'STREET', N'St'), (N'ST', N'St'), (N'STR', N'St');
;WITH Rebuilt AS
(
    SELECT a.AddressID,
        STRING_AGG(CONVERT(nvarchar(max),
            COALESCE(m.DisplayWord,
                CASE WHEN s.value COLLATE Latin1_General_100_BIN2 LIKE N'%[0-9]%'
                    THEN s.value ELSE dbo.AddressWordCase(s.value) END)), N' ')
            WITHIN GROUP (ORDER BY s.ordinal) AS AddressText
    FROM #AddressWork AS a
    CROSS APPLY STRING_SPLIT(a.CleanAddress, N' ', 1) AS s
    LEFT JOIN #StreetWords AS m
        ON m.MatchWord COLLATE Latin1_General_100_BIN2 =
            UPPER(REPLACE(s.value, N'.', N'')) COLLATE Latin1_General_100_BIN2
    WHERE s.value <> N''
    GROUP BY a.AddressID
)
UPDATE a
SET CleanAddress = r.AddressText
FROM #AddressWork AS a
JOIN Rebuilt AS r ON r.AddressID = a.AddressID;

Postal Codes Are Text With Country Rules

Keep postal codes as text so leading zeros survive. For the American example, remove embedded spaces, then display nine digits with a hyphen after the fifth. Preserve an already supplied hyphen. Do not apply this rule to countries with different postal formats.

A valid shape is five digits or five digits, a hyphen, and four digits. It still does not prove the code exists or belongs to the street. Never manufacture a missing digit. Keep the original available when a person must resolve the mismatch.

Cleaning address data needs these boundaries spelled out. A formatting rule helps comparison. Geographic correctness requires separate authoritative reference data and a defined review process. An attractive postal string cannot certify delivery.

UPDATE #AddressWork
SET CleanPostal = REPLACE(CleanPostal, N' ', N'')
WHERE CountryCode = 'US';
UPDATE #AddressWork
SET CleanPostal = STUFF(CleanPostal, 6, 0, N'-')
WHERE CountryCode = 'US'
  AND LEN(CleanPostal) = 9
  AND CleanPostal COLLATE Latin1_General_100_BIN2 NOT LIKE N'%[^0-9]%';

Flag the Rows That Still Need Attention

Build a review flag from explicit rules. This example flags blank addresses, unsupported countries, and postal values outside the American shapes. DATALENGTH checks storage length alongside the character pattern. The rules identify candidates, not every possible address defect.

Add separate checks for line-length limits, missing unit numbers when known, and conflicting country data. Keep the reasons distinguishable in a production review table. One flag without a reason leaves the reviewer guessing why the pipeline rejected a value.

UPDATE #AddressWork
SET NeedsReview = CASE
    WHEN CountryCode <> 'US' THEN 1
    WHEN NULLIF(TRIM(CleanAddress), N'') IS NULL THEN 1
    WHEN CleanPostal IS NULL THEN 1
    WHEN (DATALENGTH(CleanPostal) = 10
            AND CleanPostal COLLATE Latin1_General_100_BIN2 LIKE N'[0-9][0-9][0-9][0-9][0-9]')
      OR (DATALENGTH(CleanPostal) = 20
            AND CleanPostal COLLATE Latin1_General_100_BIN2 LIKE N'[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
        THEN 0
    ELSE 1 END;
SELECT AddressID, CountryCode, OriginalAddress, CleanAddress,
    OriginalPostal, CleanPostal, NeedsReview
FROM #AddressWork
ORDER BY NeedsReview DESC, AddressID;

Review Casing Before Making It Permanent

I review changed tokens rather than trusting a function's name. Proper casing can damage a name whose capitals are meaningful. The helper also treats only spaces as word boundaries. A production exception list should retain approved spellings and unit identifiers. Which changes are safe for your address population, and which need a reviewer?

Re-run the complete cleaning pipeline from the raw fields and compare results. The same input and rule version should produce the same derived values. Record the rule version when you store cleaned values permanently. That makes later changes explainable instead of creating unexplained edits.

For large workloads, evaluate the scalar helper's plan and cost before choosing it as the permanent implementation. A simple teaching function demonstrates the rule. It is not a promise of high-throughput performance on every address volume.

Keep Deduplication Apart From Cleaning Address Data

Matching normalized strings creates duplicate candidates, not automatic proof of one household or customer. Two units share a street address. Different people share a postal code. Retain unit, country, and source identity when evaluating a possible match.

Cleaning address data works best as a reversible preparation step. Preserve the entered evidence, apply bounded rules, and route uncertainty to review. Then the cleaner text supports better comparison without replacing human judgment with confident punctuation.

Related reading on this blog: Where Should a Data Quality Check Live? Gates, Controls, and Quarantine and "Clean Data" Is Not a Requirement: Writing Rules People Can Act On.

A clean address is not a verified destination, it is a normalized value you can still trace.

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