Free US State List

Download Free US States 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.

Share this article!    Bookmark Free US State List at Delicious Digg Free US State List Share Free US State List with your Twitter followers Share Free US State List at StumbleUpon Add Free US State List to your Technorati favorites Share Free US State List on Facebook Share Free US State List on Blinklist Share Free US State List on Diigo Share Free US State List on Reddit Share Free US State List on Linked In Seed the vine with Free US State List Share Free US State List on Mixx Share Free US State List on DZone