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.

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