Author: James Avery
URL: http://www.novicksoftware.com/UDFofWeek/Vol2/T-SQL-UDF-Vol-2-Num-48-udf_Addr_Zip5ToST.htm
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.
