Free US State List
Our free list of us states contains 51 precise data records for US states and 9 data records for places like Puerto Rico or Guam which are not states but are in one way or another associated with US and issued so called 'ANSI State Codes for the Outlying Areas of the United States and the Freely Associated States'. The data is formatted as a CSV file and can be imported into MySQL, Microsoft SQL Server, Oracle, PostgreSQL, or any other database. This list is a great time saver! You may use it as an information source, a web development resource, for a development project or whatever else comes to your mind. Our us states list is completely free for commercial and non-commercial use.
Fields in the US States List
| Name | Type | Description |
|---|---|---|
| state_code | Character | The the unique two number code and unique two letter alphabetic code for a US State as specified in INCITS 38:200x, (Formerly FIPS 5-2) Codes for the Identification of the States, the District of Columbia, Puerto Rico, and the Insular Areas of the United States. |
| state_abbrev | Character | |
| state_full | Character | Full name of the state, for example 'Rhode Island' |
| state_long | Character | Long name of the state, for example 'State of Rhode Island and Providence Plantations' |
| country_abbrev | Character | The unique two letter alphabetic code of the country and the unique full name of the country. These fields are necessary because some of the territories are really independent nations that operate under 'Compact of Free Association with the United States of America'. |
| country_full | Character |
How to import the US States List to MySQL
First create a table for the data using something like this:
CREATE TABLE `us_states` ( `state_code` varchar(2) NOT NULL, `state_abbrev` varchar(2) NOT NULL, `state_full` varchar(30) NOT NULL, `state_long` varchar(48) NOT NULL, `country_abbrev` varchar(2) NOT NULL, `country_name` varchar(30) NOT NULL, PRIMARY KEY (`state_code`) ) CHARSET=utf8;
Of course you may decide to use another table name. The next and final step is to run the 'load data' command to place the data in the table created in the first step.
load data infile 'your_path_here/us_states.csv' into table us_states fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n' ignore 1 lines;
What if the target database is on a remote computer? In that case you first need to connect your MySQL Command Line Client to the remote server. Something like this
mysql --host=www.yourdomainname.com -Dyourdatabasename --password=yourpassword --user=yourusername
Then use the 'load data' command but this time with the 'local' optionm
load data local infile 'your_path_here/us_states.csv' into table us_states fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n' ignore 1 lines;
How to get just the list of all US 'true' states?
The list of the true US states can be created by running a simple SQL query
SELECT * FROM us_states WHERE state_code <= 56 and state_code <> 11;
NOTE: This list can be used in conjunction with US City and County List and Database for web developers, US Cemetery List and Database for web developers, and US Churches List and Database for web developers.
