Sometimes a zip code is presented in a database with all nine numbers in the same column that has only five numbers. This query below will assist in formatting the numbers properly.
SELECT CASE WHEN len(zip) = 10 AND substring(zip, 6, 1) = '-' THEN zip WHEN len(zip) = 5 THEN zip WHEN len(zip) = 9 THEN left(zip, 5) + '-' + right(zip, 4) ELSE NULL END FROM address
UPDATE address SET zip = ( CASE WHEN len(zip) = 10 AND substring(zip, 6, 1) = '-' THEN zip WHEN len(zip) = 5 THEN zip WHEN len(zip) = 9 THEN left(zip, 5) + '-' + right(zip, 4) ELSE NULL END )
Source: http://forums.sqlmag.com/forums/aft/77318
Originally Posted on November 11, 2013
Last Updated on October 26, 2015
Last Updated on October 26, 2015
All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.