CODEWITHSUNDEEP
sqlsql-server
splatto
splatto

Reputation: 3227

SQL to standardize PostalCode data

I have a SQL Server table with one column that stores Canadian Postal Codes. Sometimes they're in the proper format H0H 0H0, and othertimes they're written like H0H0H0 or H0H-0H0. I'm looking to be able to update all records to standardize the format as H0H 0H0 but I cannot figure out the SQL. Any help would be appreciated.

Upvotes: 0

Views: 7110

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74307

Assuming that the following constraints hold:

  • the field in question is always at least 6 characters in length.
  • the first segment of the postal code is in columns 1-3 of the field
  • the last segment is in the rightmost 3 columns of the field.
  • There are zero or more extraneous characters located at column 4.

This should do the trick, for any number of characters in the field.:

update PostalCodes
set PostalCode = stuff( PostalCode , 4 , len(PostalCode) - 6 , ' ' )

Alternatively, you could do something like this — a case statement to look for all the different ways people have found to create junk postal codes and treat each according to its needs:

update PostalCodes
set PostalCode = case
                 when PostalCode like '[A-Z][0-9][A-Z][ -][0-9][A-Z][0-9]' then ...
                 when PostalCode like '![A-Z][0-9][A-Z][0-9][A-Z][0-9]'    then ...
                 ...
                 else PostalCode 
                 end

A select statement like

select PostalCode,count(*) from PostalCodes where PostalCode not like '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]' group by PostalCode order by 2 desc

should show you the junk data.

Upvotes: 0

judda
judda

Reputation: 3972

UPDATE PostalCodes SET PostalCode = LEFT(PostalCode, 3) + ' ' + RIGHT(PostalCode, 3 )

This assumes that the postal code field has already been trimmed on both the left and right sides. It literally grabs the left 3 digits, places a space and then the right 3 digits.

Upvotes: 5

Related Questions