Format Zip Code Properly

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