States and Their Postal Codes

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.


{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')