Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

States and Their Postal Codes

RSS
Modified on Mon, Feb 28, 2011, 1:35 PM by Administrator Categorized as General Information

U.S. States

{copytext|UsStates}
<States>
  <State>
    <Abbreviation>AL</Abbreviation>
    <Name>Alabama</Name>
  </State>
  <State>
    <Abbreviation>AK</Abbreviation>
    <Name>Alaska</Name>
  </State>
  <State>
    <Abbreviation>AZ</Abbreviation>
    <Name>Arizona</Name>
  </State>
  <State>
    <Abbreviation>AR</Abbreviation>
    <Name>Arkansas</Name>
  </State>
  <State>
    <Abbreviation>CA</Abbreviation>
    <Name>California</Name>
  </State>
  <State>
    <Abbreviation>CO</Abbreviation>
    <Name>Colorado</Name>
  </State>
  <State>
    <Abbreviation>CT</Abbreviation>
    <Name>Connecticut</Name>
  </State>
  <State>
    <Abbreviation>DE</Abbreviation>
    <Name>Delaware</Name>
  </State>
  <State>
    <Abbreviation>FL</Abbreviation>
    <Name>Florida</Name>
  </State>
  <State>
    <Abbreviation>GA</Abbreviation>
    <Name>Georgia</Name>
  </State>
  <State>
    <Abbreviation>HI</Abbreviation>
    <Name>Hawaii</Name>
  </State>
  <State>
    <Abbreviation>ID</Abbreviation>
    <Name>Idaho</Name>
  </State>
  <State>
    <Abbreviation>IL</Abbreviation>
    <Name>Illinois</Name>
  </State>
  <State>
    <Abbreviation>IN</Abbreviation>
    <Name>Indiana</Name>
  </State>
  <State>
    <Abbreviation>IA</Abbreviation>
    <Name>Iowa</Name>
  </State>
  <State>
    <Abbreviation>KS</Abbreviation>
    <Name>Kansas</Name>
  </State>
  <State>
    <Abbreviation>KY</Abbreviation>
    <Name>Kentucky</Name>
  </State>
  <State>
    <Abbreviation>LA</Abbreviation>
    <Name>Louisiana</Name>
  </State>
  <State>
    <Abbreviation>ME</Abbreviation>
    <Name>Maine</Name>
  </State>
  <State>
    <Abbreviation>MD</Abbreviation>
    <Name>Maryland</Name>
  </State>
  <State>
    <Abbreviation>MA</Abbreviation>
    <Name>Massachusetts</Name>
  </State>
  <State>
    <Abbreviation>MI</Abbreviation>
    <Name>Michigan</Name>
  </State>
  <State>
    <Abbreviation>MN</Abbreviation>
    <Name>Minnesota</Name>
  </State>
  <State>
    <Abbreviation>MS</Abbreviation>
    <Name>Mississippi</Name>
  </State>
  <State>
    <Abbreviation>MO</Abbreviation>
    <Name>Missouri</Name>
  </State>
  <State>
    <Abbreviation>MT</Abbreviation>
    <Name>Montana</Name>
  </State>
  <State>
    <Abbreviation>NE</Abbreviation>
    <Name>Nebraska</Name>
  </State>
  <State>
    <Abbreviation>NV</Abbreviation>
    <Name>Nevada</Name>
  </State>
  <State>
    <Abbreviation>NH</Abbreviation>
    <Name>New Hampshire</Name>
  </State>
  <State>
    <Abbreviation>NJ</Abbreviation>
    <Name>New Jersey</Name>
  </State>
  <State>
    <Abbreviation>NM</Abbreviation>
    <Name>New Mexico</Name>
  </State>
  <State>
    <Abbreviation>NY</Abbreviation>
    <Name>New York</Name>
  </State>
  <State>
    <Abbreviation>NC</Abbreviation>
    <Name>North Carolina</Name>
  </State>
  <State>
    <Abbreviation>ND</Abbreviation>
    <Name>North Dakota</Name>
  </State>
  <State>
    <Abbreviation>OH</Abbreviation>
    <Name>Ohio</Name>
  </State>
  <State>
    <Abbreviation>OK</Abbreviation>
    <Name>Oklahoma</Name>
  </State>
  <State>
    <Abbreviation>OR</Abbreviation>
    <Name>Oregon</Name>
  </State>
  <State>
    <Abbreviation>PA</Abbreviation>
    <Name>Pennsylvania</Name>
  </State>
  <State>
    <Abbreviation>RI</Abbreviation>
    <Name>Rhode Island</Name>
  </State>
  <State>
    <Abbreviation>SC</Abbreviation>
    <Name>South Carolina</Name>
  </State>
  <State>
    <Abbreviation>SD</Abbreviation>
    <Name>South Dakota</Name>
  </State>
  <State>
    <Abbreviation>TN</Abbreviation>
    <Name>Tennessee</Name>
  </State>
  <State>
    <Abbreviation>TX</Abbreviation>
    <Name>Texas</Name>
  </State>
  <State>
    <Abbreviation>UT</Abbreviation>
    <Name>Utah</Name>
  </State>
  <State>
    <Abbreviation>VT</Abbreviation>
    <Name>Vermont</Name>
  </State>
  <State>
    <Abbreviation>VA</Abbreviation>
    <Name>Virginia</Name>
  </State>
  <State>
    <Abbreviation>WA</Abbreviation>
    <Name>Washington</Name>
  </State>
  <State>
    <Abbreviation>WV</Abbreviation>
    <Name>West Virginia</Name>
  </State>
  <State>
    <Abbreviation>WI</Abbreviation>
    <Name>Wisconsin</Name>
  </State>
  <State>
    <Abbreviation>WY</Abbreviation>
    <Name>Wyoming</Name>
  </State>
</States>

Canadian Provinces

{copytext|CanadianProvinces}
<States>
  <State>
    <Abbreviation>AB</Abbreviation>
    <Name>Alberta</Name>
  </State>
  <State>
    <Abbreviation>BC</Abbreviation>
    <Name>British Columbia</Name>
  </State>
  <State>
    <Abbreviation>MB</Abbreviation>
    <Name>Manitoba</Name>
  </State>
  <State>
    <Abbreviation>NB</Abbreviation>
    <Name>New Brunswick</Name>
  </State>
  <State>
    <Abbreviation>NL</Abbreviation>
    <Name>Newfoundland and Labrador</Name>
  </State>
  <State>
    <Abbreviation>NS</Abbreviation>
    <Name>Nova Scotia</Name>
  </State>
  <State>
    <Abbreviation>ON</Abbreviation>
    <Name>Ontario</Name>
  </State>
  <State>
    <Abbreviation>PE</Abbreviation>
    <Name>Prince Edward Island</Name>
  </State>
  <State>
    <Abbreviation>QC</Abbreviation>
    <Name>Quebec</Name>
  </State>
  <State>
    <Abbreviation>SK</Abbreviation>
    <Name>Saskatchewan</Name>
  </State>
</States>

Lookup Table in SQL Server

The following T-SQL script creates a lookup table containing all the U.S. States, Territories, and DC as well as all the Canadian Provinces and Territories. This table is called Config.Region. The regions can be filter by the following group codes.

  • USS = U.S. State
  • UST = U.S. Territory or District
  • CDP = Canadian Province
  • CDT = Canadian Territory

{copytext|sql}
/*-- Create Schema ------------------------------------------------------------------------------*/
create schema Config    
go
/*-- Create Table -------------------------------------------------------------------------------*/
if OBJECT_ID(N'Config.Region',N'U') is null begin
    CREATE TABLE Config.Region(
	    RegionID        int IDENTITY(1,1)   NOT NULL,
	    Name            varchar(31)         NOT NULL,
	    Abbreviation    varchar(2)          NOT NULL,
        CONSTRAINT PK_Region PRIMARY KEY CLUSTERED 
        (
	        RegionID ASC
        ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
            ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    end
GO
/*-- Populate Table -----------------------------------------------------------------------------*/
declare @data xml = '<RegionalGroups>
  <RegionalGroup Code="USS" Name="U.S. State">
    <Region>
      <Abbreviation>AL</Abbreviation>
      <Name>Alabama</Name>
    </Region>
    <Region>
      <Abbreviation>AK</Abbreviation>
      <Name>Alaska</Name>
    </Region>
    <Region>
      <Abbreviation>AZ</Abbreviation>
      <Name>Arizona</Name>
    </Region>
    <Region>
      <Abbreviation>AR</Abbreviation>
      <Name>Arkansas</Name>
    </Region>
    <Region>
      <Abbreviation>CA</Abbreviation>
      <Name>California</Name>
    </Region>
    <Region>
      <Abbreviation>CO</Abbreviation>
      <Name>Colorado</Name>
    </Region>
    <Region>
      <Abbreviation>CT</Abbreviation>
      <Name>Connecticut</Name>
    </Region>
    <Region>
      <Abbreviation>DE</Abbreviation>
      <Name>Delaware</Name>
    </Region>
    <Region>
      <Abbreviation>FL</Abbreviation>
      <Name>Florida</Name>
    </Region>
    <Region>
      <Abbreviation>GA</Abbreviation>
      <Name>Georgia</Name>
    </Region>
    <Region>
      <Abbreviation>HI</Abbreviation>
      <Name>Hawaii</Name>
    </Region>
    <Region>
      <Abbreviation>ID</Abbreviation>
      <Name>Idaho</Name>
    </Region>
    <Region>
      <Abbreviation>IL</Abbreviation>
      <Name>Illinois</Name>
    </Region>
    <Region>
      <Abbreviation>IN</Abbreviation>
      <Name>Indiana</Name>
    </Region>
    <Region>
      <Abbreviation>IA</Abbreviation>
      <Name>Iowa</Name>
    </Region>
    <Region>
      <Abbreviation>KS</Abbreviation>
      <Name>Kansas</Name>
    </Region>
    <Region>
      <Abbreviation>KY</Abbreviation>
      <Name>Kentucky</Name>
    </Region>
    <Region>
      <Abbreviation>LA</Abbreviation>
      <Name>Louisiana</Name>
    </Region>
    <Region>
      <Abbreviation>ME</Abbreviation>
      <Name>Maine</Name>
    </Region>
    <Region>
      <Abbreviation>MD</Abbreviation>
      <Name>Maryland</Name>
    </Region>
    <Region>
      <Abbreviation>MA</Abbreviation>
      <Name>Massachusetts</Name>
    </Region>
    <Region>
      <Abbreviation>MI</Abbreviation>
      <Name>Michigan</Name>
    </Region>
    <Region>
      <Abbreviation>MN</Abbreviation>
      <Name>Minnesota</Name>
    </Region>
    <Region>
      <Abbreviation>MS</Abbreviation>
      <Name>Mississippi</Name>
    </Region>
    <Region>
      <Abbreviation>MO</Abbreviation>
      <Name>Missouri</Name>
    </Region>
    <Region>
      <Abbreviation>MT</Abbreviation>
      <Name>Montana</Name>
    </Region>
    <Region>
      <Abbreviation>NE</Abbreviation>
      <Name>Nebraska</Name>
    </Region>
    <Region>
      <Abbreviation>NV</Abbreviation>
      <Name>Nevada</Name>
    </Region>
    <Region>
      <Abbreviation>NH</Abbreviation>
      <Name>New Hampshire</Name>
    </Region>
    <Region>
      <Abbreviation>NJ</Abbreviation>
      <Name>New Jersey</Name>
    </Region>
    <Region>
      <Abbreviation>NM</Abbreviation>
      <Name>New Mexico</Name>
    </Region>
    <Region>
      <Abbreviation>NY</Abbreviation>
      <Name>New York</Name>
    </Region>
    <Region>
      <Abbreviation>NC</Abbreviation>
      <Name>North Carolina</Name>
    </Region>
    <Region>
      <Abbreviation>ND</Abbreviation>
      <Name>North Dakota</Name>
    </Region>
    <Region>
      <Abbreviation>OH</Abbreviation>
      <Name>Ohio</Name>
    </Region>
    <Region>
      <Abbreviation>OK</Abbreviation>
      <Name>Oklahoma</Name>
    </Region>
    <Region>
      <Abbreviation>OR</Abbreviation>
      <Name>Oregon</Name>
    </Region>
    <Region>
      <Abbreviation>PA</Abbreviation>
      <Name>Pennsylvania</Name>
    </Region>
    <Region>
      <Abbreviation>RI</Abbreviation>
      <Name>Rhode Island</Name>
    </Region>
    <Region>
      <Abbreviation>SC</Abbreviation>
      <Name>South Carolina</Name>
    </Region>
    <Region>
      <Abbreviation>SD</Abbreviation>
      <Name>South Dakota</Name>
    </Region>
    <Region>
      <Abbreviation>TN</Abbreviation>
      <Name>Tennessee</Name>
    </Region>
    <Region>
      <Abbreviation>TX</Abbreviation>
      <Name>Texas</Name>
    </Region>
    <Region>
      <Abbreviation>UT</Abbreviation>
      <Name>Utah</Name>
    </Region>
    <Region>
      <Abbreviation>VT</Abbreviation>
      <Name>Vermont</Name>
    </Region>
    <Region>
      <Abbreviation>VA</Abbreviation>
      <Name>Virginia</Name>
    </Region>
    <Region>
      <Abbreviation>WA</Abbreviation>
      <Name>Washington</Name>
    </Region>
    <Region>
      <Abbreviation>WV</Abbreviation>
      <Name>West Virginia</Name>
    </Region>
    <Region>
      <Abbreviation>WI</Abbreviation>
      <Name>Wisconsin</Name>
    </Region>
    <Region>
      <Abbreviation>WY</Abbreviation>
      <Name>Wyoming</Name>
    </Region>
  </RegionalGroup>
  <RegionalGroup Code="UST" Name="U.S. Territory or District">
    <Region>
      <Abbreviation>AS</Abbreviation>
      <Name>American Samoa </Name>
    </Region>
    <Region>
      <Abbreviation>DC</Abbreviation>
      <Name>District of Columbia </Name>
    </Region>
    <Region>
      <Abbreviation>FM</Abbreviation>
      <Name>Federated Regions of Micronesia</Name>
    </Region>
    <Region>
      <Abbreviation>GU</Abbreviation>
      <Name>Guam </Name>
    </Region>
    <Region>
      <Abbreviation>MH</Abbreviation>
      <Name>Marshall Islands </Name>
    </Region>
    <Region>
      <Abbreviation>MP</Abbreviation>
      <Name>Northern Mariana Islands </Name>
    </Region>
    <Region>
      <Abbreviation>PW</Abbreviation>
      <Name>Palau </Name>
    </Region>
    <Region>
      <Abbreviation>PR</Abbreviation>
      <Name>Puerto Rico </Name>
    </Region>
    <Region>
      <Abbreviation>VI</Abbreviation>
      <Name>Virgin Islands </Name>
    </Region>
  </RegionalGroup>
  <RegionalGroup Code="CDP" Name="Canadian Province">
    <Region>
      <Abbreviation>AB</Abbreviation>
      <Name>Alberta</Name>
    </Region>
    <Region>
      <Abbreviation>BC</Abbreviation>
      <Name>British Columbia</Name>
    </Region>
    <Region>
      <Abbreviation>MB</Abbreviation>
      <Name>Manitoba</Name>
    </Region>
    <Region>
      <Abbreviation>NB</Abbreviation>
      <Name>New Brunswick</Name>
    </Region>
    <Region>
      <Abbreviation>NL</Abbreviation>
      <Name>Newfoundland and Labrador</Name>
    </Region>
    <Region>
      <Abbreviation>NS</Abbreviation>
      <Name>Nova Scotia</Name>
    </Region>
    <Region>
      <Abbreviation>ON</Abbreviation>
      <Name>Ontario</Name>
    </Region>
    <Region>
      <Abbreviation>PE</Abbreviation>
      <Name>Prince Edward Island</Name>
    </Region>
    <Region>
      <Abbreviation>QC</Abbreviation>
      <Name>Quebec</Name>
    </Region>
    <Region>
      <Abbreviation>SK</Abbreviation>
      <Name>Saskatchewan</Name>
    </Region>
  </RegionalGroup>
  <RegionalGroup Code="CDT" Name="Canadian Territory">
    <Region>
      <Abbreviation>NT</Abbreviation>
      <Name>Northwest Territories</Name>
    </Region>
    <Region>
      <Abbreviation>NU</Abbreviation>
      <Name>Nunavut</Name>
    </Region>
    <Region>
      <Abbreviation>YT</Abbreviation>
      <Name>Yukon</Name>
    </Region>
  </RegionalGroup>
</RegionalGroups>'
;
with x as (
    select
         Name           = T.c.value('Name[1]','varchar(31)')
        ,Abbreviation   = T.c.value('Abbreviation[1]','varchar(2)')
        ,GroupCode      = T.c.value('../@Code','varchar(3)')
        ,GroupName      = T.c.value('../@Name','varchar(26)')
    from   
        @data.nodes('/RegionalGroups/RegionalGroup/Region') T(c)
    )
insert into Config.Region (
     Name
    ,Abbreviation
    )    
select 
     Name
    ,Abbreviation
from
    x
where 1=1
    --and GroupCode in ('USS','UST')

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.