Mapping of US States with the two character State ID

Lina_Sengupta · April 21, 2016, 1:02pm

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?

NormTeamT · April 30, 2016, 7:27am

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]

NormTeamT · April 30, 2016, 7:40am

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: