State Name and Abbreviation Lookup Table Script

This is a quick script which creates a table consisting of State Names and their corresponding Abbreviations. I use this table when doing basic data quality checking and also standardizing state designations.

CREATE TABLE dbo.StateLookup
   StateID       INT IDENTITY (1, 1),
   StateName     VARCHAR (32),
   StateAbbrev   CHAR (2),
VALUES ('Alabama', 'AL'),
       ('Alaska', 'AK'),
       ('Arizona', 'AZ'),
       ('Arkansas', 'AR'),
       ('California', 'CA'),
       ('Colorado', 'CO'),
       ('Connecticut', 'CT'),
       ('Delaware', 'DE'),
       ('District of Columbia', 'DC'),
       ('Florida', 'FL'),
       ('Georgia', 'GA'),
       ('Hawaii', 'HI'),
       ('Idaho', 'ID'),
       ('Illinois', 'IL'),
       ('Indiana', 'IN'),
       ('Iowa', 'IA'),
       ('Kansas', 'KS'),
       ('Kentucky', 'KY'),
       ('Louisiana', 'LA'),
       ('Maine', 'ME'),
       ('Maryland', 'MD'),
       ('Massachusetts', 'MA'),
       ('Michigan', 'MI'),
       ('Minnesota', 'MN'),
       ('Mississippi', 'MS'),
       ('Missouri', 'MO'),
       ('Montana', 'MT'),
       ('Nebraska', 'NE'),
       ('Nevada', 'NV'),
       ('New Hampshire', 'NH'),
       ('New Jersey', 'NJ'),
       ('New Mexico', 'NM'),
       ('New York', 'NY'),
       ('North Carolina', 'NC'),
       ('North Dakota', 'ND'),
       ('Ohio', 'OH'),
       ('Oklahoma', 'OK'),
       ('Oregon', 'OR'),
       ('Pennsylvania', 'PA'),
       ('Rhode Island', 'RI'),
       ('South Carolina', 'SC'),
       ('South Dakota', 'SD'),
       ('Tennessee', 'TN'),
       ('Texas', 'TX'),
       ('Utah', 'UT'),
       ('Vermont', 'VT'),
       ('Virginia', 'VA'),
       ('Washington', 'WA'),
       ('West Virginia', 'WV'),
       ('Wisconsin', 'WI'),
       ('Wyoming', 'WY')

Also, you can add common misspellings to the table such as ‘Pensylvania’ to further clean up your data. If your data set is international, you may want to add Canadian provinces as well. One way I use this table is with a SQL Server Integration Services (SSIS) Lookup transform and the following query.

SELECT StateName State, StateAbbrev FROM StateLookup
SELECT StateAbbrev State, StateAbbrev FROM StateLookup

This standardizes everything to 2 character states abbreviations. Any records which don’t match can be handed in another branch of the data flow through fuzzy matching or another method.

Do you use anything similar?

Related posts:

1 comment to State Name and Abbreviation Lookup Table Script

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>