Mapping of US States with the two character State ID

Toad for Oracle & DevOps Toolkit
1

Is there any pre existing table for Mapping of US States with the two character State ID?

I am parsing user entry for US states and would like to get the two character State ID.

Are there any recommendations?

2

On 21/04/16 21:10, Lina Sengupta wrote:

*Mapping of US States with the two character State ID

CREATE TABLE STATES_TABLE (

STATE_CODE CHAR(2) NOT NULL,

STATE_NAME VARCHAR2(35) NOT NULL

);

INSERT INTO STATES_TABLE VALUES ('AL', 'Alabama');

INSERT INTO STATES_TABLE VALUES ('AK', 'Alaska');

INSERT INTO STATES_TABLE VALUES ('AS', 'American Samoa');

INSERT INTO STATES_TABLE VALUES ('AZ', 'Arizona');

INSERT INTO STATES_TABLE VALUES ('AR', 'Arkansas');

INSERT INTO STATES_TABLE VALUES ('CA', 'California');

INSERT INTO STATES_TABLE VALUES ('CO', 'Colorado');

INSERT INTO STATES_TABLE VALUES ('CT', 'Connecticut');

INSERT INTO STATES_TABLE VALUES ('DE', 'Delaware');

INSERT INTO STATES_TABLE VALUES ('DC', 'District Of Columbia');

INSERT INTO STATES_TABLE VALUES ('FM', 'Federated States Of Micronesia');

INSERT INTO STATES_TABLE VALUES ('FL', 'Florida');

INSERT INTO STATES_TABLE VALUES ('GA', 'Georgia');

INSERT INTO STATES_TABLE VALUES ('GU', 'Guam');

INSERT INTO STATES_TABLE VALUES ('HI', 'Hawaii');

INSERT INTO STATES_TABLE VALUES ('ID', 'Idaho');

INSERT INTO STATES_TABLE VALUES ('IL', 'Illinois');

INSERT INTO STATES_TABLE VALUES ('IN', 'Indiana');

INSERT INTO STATES_TABLE VALUES ('IA', 'Iowa');

INSERT INTO STATES_TABLE VALUES ('KS', 'Kansas');

INSERT INTO STATES_TABLE VALUES ('KY', 'Kentucky');

INSERT INTO STATES_TABLE VALUES ('LA', 'Louisiana');

INSERT INTO STATES_TABLE VALUES ('ME', 'Maine');

INSERT INTO STATES_TABLE VALUES ('MH', 'Marshall Islands');

INSERT INTO STATES_TABLE VALUES ('MD', 'Maryland');

INSERT INTO STATES_TABLE VALUES ('MA', 'Massachusetts');

INSERT INTO STATES_TABLE VALUES ('MI', 'Michigan');

INSERT INTO STATES_TABLE VALUES ('MN', 'Minnesota');

INSERT INTO STATES_TABLE VALUES ('MS', 'Mississippi');

INSERT INTO STATES_TABLE VALUES ('MO', 'Missouri');

INSERT INTO STATES_TABLE VALUES ('MT', 'Montana');

INSERT INTO STATES_TABLE VALUES ('NE', 'Nebraska');

INSERT INTO STATES_TABLE VALUES ('NV', 'Nevada');

INSERT INTO STATES_TABLE VALUES ('NH', 'New Hampshire');

INSERT INTO STATES_TABLE VALUES ('NJ', 'New Jersey');

INSERT INTO STATES_TABLE VALUES ('NM', 'New Mexico');

INSERT INTO STATES_TABLE VALUES ('NY', 'New York');

INSERT INTO STATES_TABLE VALUES ('NC', 'North Carolina');

INSERT INTO STATES_TABLE VALUES ('ND', 'North Dakota');

INSERT INTO STATES_TABLE VALUES ('MP', 'Northern Mariana Islands');

INSERT INTO STATES_TABLE VALUES ('OH', 'Ohio');

INSERT INTO STATES_TABLE VALUES ('OK', 'Oklahoma');

INSERT INTO STATES_TABLE VALUES ('OR', 'Oregon');

INSERT INTO STATES_TABLE VALUES ('PW', 'Palau');

INSERT INTO STATES_TABLE VALUES ('PA', 'Pennsylvania');

INSERT INTO STATES_TABLE VALUES ('PR', 'Puerto Rico');

INSERT INTO STATES_TABLE VALUES ('RI', 'Rhode Island');

INSERT INTO STATES_TABLE VALUES ('SC', 'South Carolina');

INSERT INTO STATES_TABLE VALUES ('SD', 'South Dakota');

INSERT INTO STATES_TABLE VALUES ('TN', 'Tennessee');

INSERT INTO STATES_TABLE VALUES ('TX', 'Texas');

INSERT INTO STATES_TABLE VALUES ('UT', 'Utah');

INSERT INTO STATES_TABLE VALUES ('VT', 'Vermont');

INSERT INTO STATES_TABLE VALUES ('VI', 'Virgin Islands');

INSERT INTO STATES_TABLE VALUES ('VA', 'Virginia');

INSERT INTO STATES_TABLE VALUES ('WA', 'Washington');

INSERT INTO STATES_TABLE VALUES ('WV', 'West Virginia');

INSERT INTO STATES_TABLE VALUES ('WI', 'Wisconsin');

INSERT INTO STATES_TABLE VALUES ('WY', 'Wyoming');

COMMIT;

--

Cheers,

Norm. [TeamT]

3

Hmm. The reply I sent by email appears to be somewhat truncated above. I wonder why? Never mind, here you are, this might help. There may be some “states” that are overseas territories, but you can either sort them out or leave them in accordingly.

The following should give you what you want:

CREATE TABLE STATES_TABLE (

STATE_CODE CHAR(2) NOT NULL,

STATE_NAME VARCHAR2(35) NOT NULL

);

INSERT INTO STATES_TABLE VALUES (‘AL’, ‘Alabama’);

INSERT INTO STATES_TABLE VALUES (‘AK’, ‘Alaska’);

INSERT INTO STATES_TABLE VALUES (‘AS’, ‘American Samoa’);

INSERT INTO STATES_TABLE VALUES (‘AZ’, ‘Arizona’);

INSERT INTO STATES_TABLE VALUES (‘AR’, ‘Arkansas’);

INSERT INTO STATES_TABLE VALUES (‘CA’, ‘California’);

INSERT INTO STATES_TABLE VALUES (‘CO’, ‘Colorado’);

INSERT INTO STATES_TABLE VALUES (‘CT’, ‘Connecticut’);

INSERT INTO STATES_TABLE VALUES (‘DE’, ‘Delaware’);

INSERT INTO STATES_TABLE VALUES (‘DC’, ‘District Of Columbia’);

INSERT INTO STATES_TABLE VALUES (‘FM’, ‘Federated States Of Micronesia’);

INSERT INTO STATES_TABLE VALUES (‘FL’, ‘Florida’);

INSERT INTO STATES_TABLE VALUES (‘GA’, ‘Georgia’);

INSERT INTO STATES_TABLE VALUES (‘GU’, ‘Guam’);

INSERT INTO STATES_TABLE VALUES (‘HI’, ‘Hawaii’);

INSERT INTO STATES_TABLE VALUES (‘ID’, ‘Idaho’);

INSERT INTO STATES_TABLE VALUES (‘IL’, ‘Illinois’);

INSERT INTO STATES_TABLE VALUES (‘IN’, ‘Indiana’);

INSERT INTO STATES_TABLE VALUES (‘IA’, ‘Iowa’);

INSERT INTO STATES_TABLE VALUES (‘KS’, ‘Kansas’);

INSERT INTO STATES_TABLE VALUES (‘KY’, ‘Kentucky’);

INSERT INTO STATES_TABLE VALUES (‘LA’, ‘Louisiana’);

INSERT INTO STATES_TABLE VALUES (‘ME’, ‘Maine’);

INSERT INTO STATES_TABLE VALUES (‘MH’, ‘Marshall Islands’);

INSERT INTO STATES_TABLE VALUES (‘MD’, ‘Maryland’);

INSERT INTO STATES_TABLE VALUES (‘MA’, ‘Massachusetts’);

INSERT INTO STATES_TABLE VALUES (‘MI’, ‘Michigan’);

INSERT INTO STATES_TABLE VALUES (‘MN’, ‘Minnesota’);

INSERT INTO STATES_TABLE VALUES (‘MS’, ‘Mississippi’);

INSERT INTO STATES_TABLE VALUES (‘MO’, ‘Missouri’);

INSERT INTO STATES_TABLE VALUES (‘MT’, ‘Montana’);

INSERT INTO STATES_TABLE VALUES (‘NE’, ‘Nebraska’);

INSERT INTO STATES_TABLE VALUES (‘NV’, ‘Nevada’);

INSERT INTO STATES_TABLE VALUES (‘NH’, ‘New Hampshire’);

INSERT INTO STATES_TABLE VALUES (‘NJ’, ‘New Jersey’);

INSERT INTO STATES_TABLE VALUES (‘NM’, ‘New Mexico’);

INSERT INTO STATES_TABLE VALUES (‘NY’, ‘New York’);

INSERT INTO STATES_TABLE VALUES (‘NC’, ‘North Carolina’);

INSERT INTO STATES_TABLE VALUES (‘ND’, ‘North Dakota’);

INSERT INTO STATES_TABLE VALUES (‘MP’, ‘Northern Mariana Islands’);

INSERT INTO STATES_TABLE VALUES (‘OH’, ‘Ohio’);

INSERT INTO STATES_TABLE VALUES (‘OK’, ‘Oklahoma’);

INSERT INTO STATES_TABLE VALUES (‘OR’, ‘Oregon’);

INSERT INTO STATES_TABLE VALUES (‘PW’, ‘Palau’);

INSERT INTO STATES_TABLE VALUES (‘PA’, ‘Pennsylvania’);

INSERT INTO STATES_TABLE VALUES (‘PR’, ‘Puerto Rico’);

INSERT INTO STATES_TABLE VALUES (‘RI’, ‘Rhode Island’);

INSERT INTO STATES_TABLE VALUES (‘SC’, ‘South Carolina’);

INSERT INTO STATES_TABLE VALUES (‘SD’, ‘South Dakota’);

INSERT INTO STATES_TABLE VALUES (‘TN’, ‘Tennessee’);

INSERT INTO STATES_TABLE VALUES (‘TX’, ‘Texas’);

INSERT INTO STATES_TABLE VALUES (‘UT’, ‘Utah’);

INSERT INTO STATES_TABLE VALUES (‘VT’, ‘Vermont’);

INSERT INTO STATES_TABLE VALUES (‘VI’, ‘Virgin Islands’);

INSERT INTO STATES_TABLE VALUES (‘VA’, ‘Virginia’);

INSERT INTO STATES_TABLE VALUES (‘WA’, ‘Washington’);

INSERT INTO STATES_TABLE VALUES (‘WV’, ‘West Virginia’);

INSERT INTO STATES_TABLE VALUES (‘WI’, ‘Wisconsin’);

INSERT INTO STATES_TABLE VALUES (‘WY’, ‘Wyoming’);

COMMIT;

–

Cheers,

Norm. [TeamT]

4

Sorry, the web page appears to have fragged the line spacing. Sigh.

5

And I wouldn’t bother putting an index or primary key on the above. Oracle will probably never use it as the table will fit into one or two 8k blocks and will always be read with a full scan. :slight_smile:

6

You can find the official USPS abbreviations at about.usps.com/…/state-abbreviations.htm

HTH – Mark D Powell –