CODEWITHSUNDEEP
sqlzipcode
Petar Ivanov
Petar Ivanov

Reputation: 1

Presenting null values when field is null zip codes

I am trying to figure this out. I am manipulating ZIP codes, but I am trying to add an overall function that would perform the following:

If the field you are looking at is blank (EMPTY) return/write (BLANK) into the new field.

Currently the following is happening: Everything that is less than 5 characters gets either a leading zero or if it is blank it gets 00000.

      update ImportStagingContact
         set zip = '0' + zip
       where len(zip) = 8
         and ImportLogID = @nImportLogID

      update ImportStagingContact
         set zip = replace(zip, '-', '')
       where len(zip) = 10
         and charindex('-', zip) > 0
         and ImportLogID = @nImportLogID

      update ImportStagingContact
         set zip = right('00000' + zip, 5)
       where len(zip) < 5
         and ImportLogID = @nImportLogID

      update ImportStagingContact
         set zip = left(zip, 5)
       where len(zip) = 9
         and ImportLogID = @nImportLogID

Upvotes: 0

Views: 819

Answers (1)

Brad
Brad

Reputation: 3601

UPDATE:

What is your desired results? A 5 digit or 9 digit zip? Let me know and I can update to work better

Looking at your logic more closely this may not work depending on what your desired results you want.

The first update is setting the zip to 9 digits, but the bottom 2 are limiting it to 5 digits it looks like. SO you will get inconsistent results if using the case statement?

you may need to do 2 updates for 5 or 9 digit case and one to clean the data (remove the -) (or can combine that in others).

Depending on your technology, this shoudl work in MSSQL. You can change the order of the case statement to so the one you want to run first is above the others. If not MSSQL, logic should be same bug syntax may be different.

UPDATE ImportStagingContact
SET zip = 
    CASE
        -- to set to blank, maybe set to null if that is desired vs blank 
        WHEN len(ISNULL(zip, '') = 0 then '' 
        WHEN len(zip) = 8 THEN '0' + zip
        WHEN len(zip) = 10 and charindex('-', zip) > 0 THEN replace(zip, '-', '')
        WHEN len(zip) < 5 THEN right('00000' + zip, 5) 
        WHEN len(zip) = 9 THEN left(zip, 5)
    END
where ImportLogID = @nImportLogID

Upvotes: 1

Related Questions