Translate ZIP Codes to State Codes

It's time to mail your holiday cards. Whether it's Christmas, New Years, Chanukah, or something else, there are going to be billions of cards sent in the next few weeks. One of the tasks that my wife and I face each year is getting all the addresses correct, which usually means finding ZIP codes for the partial addresses that we have for new friends.

For those of you outside the US, the ZIP code is the U. S. Postal Office's postal code scheme. There are two versions, the original 5 digit code and the newer extended 9 digit code, which adds 4 digits to make the code more specific to how the letter is delivered.

When the original scheme was created they used the first two digits to indicte the state or territory where the mail was headed. That design has survived and as you'll see can be used to go backwards from the five digit Zip code to the state abreviation.

This article's UDF is udf_Addr_Zip5ToST, which translates a zip code into a state code. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE  FUNCTION dbo.udf_Addr_Zip5ToST (

    @Zip5 char(5) -- 5 digit zip code to translate.
)   RETURNS char(2) -- State code corresponding to the ZIP code
/*
* Translates from a 5 digit ZIP code to a two character state
* code. ZIP codes are United States Postal Service codes.  
* Data is subject to occasional additions.  
*
* Example:
select dbo.udf_Addr_Zip5ToST('01776') as [State Code]
* Test:
PRINT 'Test 1    ' + CASE WHEN 'MA' = 
dbo.udf_Addr_Zip5ToST ('01776') THEN 'Worked' ELSE 'ERROR' END
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as the T-SQL UDF of the Week for Vol 3 #48 12/7/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

DECLARE @nZIP int -- integer version of the zip code

IF @ZIP5 IS NULL RETURN NULL
IF NOT 1=ISNUMERIC(@ZIP5) RETURN NULL

SET @nZIP = convert(int , @ZIP5)
  
RETURN CASE 
    WHEN @nZIP BETWEEN 99501 AND 99950 THEN 'AK' -- Alaska 
    WHEN @nZIP BETWEEN 35004 AND 36925 THEN 'AL' -- Alabama 
    WHEN @nZIP BETWEEN 71601 AND 72959 THEN 'AR' -- Arkansas 
    WHEN @nZIP BETWEEN 75502 AND 75502 THEN 'AR' -- Arkansas( Texarkana)
    WHEN @nZIP BETWEEN 85001 AND 86556 THEN 'AZ' -- Arizona 
    WHEN @nZIP BETWEEN 90001 AND 96162 THEN 'CA' -- California 
    WHEN @nZIP BETWEEN 80001 AND 81658 THEN 'CO' -- Colorado 
    WHEN @nZIP BETWEEN 06001 AND 06389 THEN 'CT' -- Connecticut 
    WHEN @nZIP BETWEEN 06401 AND 06928 THEN 'CT' -- Connecticut 
    WHEN @nZIP BETWEEN 20001 AND 20039 THEN 'DC' -- Dist of Columbia 
    WHEN @nZIP BETWEEN 20042 AND 20599 THEN 'DC' -- Dist of Columbia 
    WHEN @nZIP BETWEEN 20799 AND 20799 THEN 'DC' -- Dist of Columbia 
    WHEN @nZIP BETWEEN 19701 AND 19980 THEN 'DE' -- Delaware 
    WHEN @nZIP BETWEEN 32004 AND 34997 THEN 'FL' -- Florida 
    WHEN @nZIP BETWEEN 30001 AND 31999 THEN 'GA' -- Georgia 
    WHEN @nZIP BETWEEN 39901 AND 39901 THEN 'GA' -- Georga (Atlanta) 
    WHEN @nZIP BETWEEN 96701 AND 96898 THEN 'HI' -- Hawaii 
    WHEN @nZIP BETWEEN 50001 AND 52809 THEN 'IA' -- Iowa 
    WHEN @nZIP BETWEEN 68119 AND 68120 THEN 'IA' -- Iowa (OMAHA) 
    WHEN @nZIP BETWEEN 83201 AND 83876 THEN 'ID' -- Idaho 
    WHEN @nZIP BETWEEN 60001 AND 62999 THEN 'IL' -- Illinois 
    WHEN @nZIP BETWEEN 46001 AND 47997 THEN 'IN' -- Indiana 
    WHEN @nZIP BETWEEN 66002 AND 67954 THEN 'KS' -- Kansas 
    WHEN @nZIP BETWEEN 40003 AND 42788 THEN 'KY' -- Kentucky 
    WHEN @nZIP BETWEEN 70001 AND 71232 THEN 'LA' -- Louisiana 
    WHEN @nZIP BETWEEN 71234 AND 71497 THEN 'LA' -- Louisiana 
    WHEN @nZIP BETWEEN 01001 AND 02791 THEN 'MA' -- Massachusetts 
    WHEN @nZIP BETWEEN 05501 AND 05544 THEN 'MA' -- Massachusetts (Andover) 
    WHEN @nZIP BETWEEN 20331 AND 20331 THEN 'MD' -- Maryland 
    WHEN @nZIP BETWEEN 20335 AND 20797 THEN 'MD' -- Maryland 
    WHEN @nZIP BETWEEN 20812 AND 21930 THEN 'MD' -- Maryland 
    WHEN @nZIP BETWEEN 03901 AND 04992 THEN 'ME' -- Maine 
    WHEN @nZIP BETWEEN 48001 AND 49971 THEN 'MI' -- Michigan 
    WHEN @nZIP BETWEEN 55001 AND 56763 THEN 'MN' -- Minnesota 
    WHEN @nZIP BETWEEN 63001 AND 65899 THEN 'MO' -- Missouri
    WHEN @nZIP BETWEEN 38601 AND 39776 THEN 'MS' -- Mississippi 
    WHEN @nZIP BETWEEN 71233 AND 71233 THEN 'MS' -- Mississippi(Warren) 
    WHEN @nZIP BETWEEN 59001 AND 59937 THEN 'MT' -- Montana 
    WHEN @nZIP BETWEEN 27006 AND 28909 THEN 'NC' -- North Carolina 
    WHEN @nZIP BETWEEN 58001 AND 58856 THEN 'ND' -- North Dakota 
    WHEN @nZIP BETWEEN 68001 AND 68118 THEN 'NE' -- Nebraska 
    WHEN @nZIP BETWEEN 68122 AND 69367 THEN 'NE' -- Nebraska
    WHEN @nZIP BETWEEN 03031 AND 03897 THEN 'NH' -- New Hampshire 
    WHEN @nZIP BETWEEN 07001 AND 08989 THEN 'NJ' -- New Jersey 
    WHEN @nZIP BETWEEN 87001 AND 88441 THEN 'NM' -- New Mexico 
    WHEN @nZIP BETWEEN 88901 AND 89883 THEN 'NV' -- Nevada 
    WHEN @nZIP BETWEEN 06390 AND 06390 THEN 'NY' -- New York (Fishers Is) 
    WHEN @nZIP BETWEEN 10001 AND 14975 THEN 'NY' -- New York 
    WHEN @nZIP BETWEEN 43001 AND 45999 THEN 'OH' -- Ohio 
    WHEN @nZIP BETWEEN 73001 AND 73199 THEN 'OK' -- Oklahoma 
    WHEN @nZIP BETWEEN 73401 AND 74966 THEN 'OK' -- Oklahoma 
    WHEN @nZIP BETWEEN 97001 AND 97920 THEN 'OR' -- Oregon 
    WHEN @nZIP BETWEEN 15001 AND 19640 THEN 'PA' -- Pennsylvania 
    WHEN @nZIP BETWEEN 02801 AND 02940 THEN 'RI' -- Rhode Island 
    WHEN @nZIP BETWEEN 29001 AND 29948 THEN 'SC' -- South Carolina 
    WHEN @nZIP BETWEEN 57001 AND 57799 THEN 'SD' -- South Dakota 
    WHEN @nZIP BETWEEN 37010 AND 38589 THEN 'TN' -- Tennessee 
    WHEN @nZIP BETWEEN 73301 AND 73301 THEN 'TX' -- Texas (Austin)
    WHEN @nZIP BETWEEN 75001 AND 75501 THEN 'TX' -- Texas 
    WHEN @nZIP BETWEEN 75503 AND 79999 THEN 'TX' -- Texas 
    WHEN @nZIP BETWEEN 88510 AND 88589 THEN 'TX' -- Texas (El Paso)
    WHEN @nZIP BETWEEN 84001 AND 84784 THEN 'UT' -- Utah 
    WHEN @nZIP BETWEEN 20040 AND 20041 THEN 'VA' -- Virginia 
    WHEN @nZIP BETWEEN 20040 AND 20167 THEN 'VA' -- Virginia 
    WHEN @nZIP BETWEEN 20042 AND 20042 THEN 'VA' -- Virginia 
    WHEN @nZIP BETWEEN 22001 AND 24658 THEN 'VA' -- Virginia 
    WHEN @nZIP BETWEEN 05001 AND 05495 THEN 'VT' -- Vermont 
    WHEN @nZIP BETWEEN 05601 AND 05907 THEN 'VT' -- Vermont 
    WHEN @nZIP BETWEEN 98001 AND 99403 THEN 'WA' -- Washington 
    WHEN @nZIP BETWEEN 53001 AND 54990 THEN 'WI' -- Wisconsin 
    WHEN @nZIP BETWEEN 24701 AND 26886 THEN 'WV' -- West Virginia
    WHEN @nZIP BETWEEN 82001 AND 83128 THEN 'WY' -- Wyoming 
    ELSE NULL
    END

END

GO

GRANT EXEC on dbo.udf_Addr_Zip5ToST TO PUBLIC
GO

The tests from the function is pretty simple: Put in the zip code and get out the state. This example has the zip codes of two important houses: The White House and my house:

SELECT dbo.udf_Addr_Zip5ToST ('20500') [White House]
     , dbo.udf_Addr_Zip5ToST ('01776') [Andy's House]
     
GO

(Results)
White House Andy's House 
----------- ------------ 
DC          MA

The code for the function does a simple lookup on the numeric range. There are some ranges of numbers that aren't in use and udf_Addr_Zip5ToST returns NULL when one of these is encountered.

This function brings up a question about normalization. When normalizing to third normal form, one of the rules is to eliminate functional dependencies between columns. That is a column can be eliminiated if it can be derived from other columns. The easy example of this is a table with these columns

 
  , Quantity int -- Quantity of goods
  , PricePerUnit money -- Price for each item.
  , PriceExtended money -- product of Quanitity and PricePerUnit

Since PriceExtended is a function of Quantity and PricePerUnit, it can be removed from the table and derived when needed. A computed column is a reasonable way to derive PriceExtended. This example was used before in an article on Indexed Views that you can read at http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm.

The question that's raised is: Now that the function udf_Addr_Zip5ToST exists, should state code columns be eliminated from tables that hold addresses in order to keep them in third normal form?

So long as your only working with addresses in the United States it's certainly possible to drop the state code, usually ST, and rely on the function to do the translation. But is it a good idea?

I'm not really sure of the answer. Of course, in most applications that I've worked on there are always a few "overseas" addresses. Usually from Canada or Mexico, so handling the exceptions is one reason for keeping the State code.

Whatever your answer to the question, it's time to get those holiday cards into the mail.

Happy Holidays,
Andy

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick