DEV Community

Syed Saadullah Shah
Syed Saadullah Shah

Posted on

MySQL table for U.S. States with ISO code

Here are some handy MySQL statements to create a table of American states and territories with their ISO abbreviations. Handy for drop-down lists and the like. We’ve also posted code for a Canadian provinces MySQL table, and an Australian one as well. Enjoy!

CREATE TABLE geo_states_us (
id INT(11) NOT NULL DEFAULT '0',
iso CHAR(2) COLLATE utf8_unicode_ci DEFAULT NULL,
printable_name CHAR(40) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO geo_states_us (id,iso,printable_name)
VALUES
(1,'AK','Alaska'),
(2,'AL','Alabama'),
(3,'AS','American Samoa'),
(4,'AZ','Arizona'),
(5,'AR','Arkansas'),
(6,'CA','California'),
(7,'CO','Colorado'),
(8,'CT','Connecticut'),
(9,'DE','Delaware'),
(10,'DC','District of Columbia'),
(11,'FM','Federated States of Micronesia'),
(12,'FL','Florida'),
(13,'GA','Georgia'),
(14,'GU','Guam'),
(15,'HI','Hawaii'),
(16,'ID','Idaho'),
(17,'IL','Illinois'),
(18,'IN','Indiana'),
(19,'IA','Iowa'),
(20,'KS','Kansas'),
(21,'KY','Kentucky'),
(22,'LA','Louisiana'),
(23,'ME','Maine'),
(24,'MH','Marshall Islands'),
(25,'MD','Maryland'),
(26,'MA','Massachusetts'),
(27,'MI','Michigan'),
(28,'MN','Minnesota'),
(29,'MS','Mississippi'),
(30,'MO','Missouri'),
(31,'MT','Montana'),
(32,'NE','Nebraska'),
(33,'NV','Nevada'),
(34,'NH','New Hampshire'),
(35,'NJ','New Jersey'),
(36,'NM','New Mexico'),
(37,'NY','New York'),
(38,'NC','North Carolina'),
(39,'ND','North Dakota'),
(40,'MP','Northern Mariana Islands'),
(41,'OH','Ohio'),
(42,'OK','Oklahoma'),
(43,'OR','Oregon'),
(44,'PW','Palau'),
(45,'PA','Pennsylvania'),
(46,'PR','Puerto Rico'),
(47,'RI','Rhode Island'),
(48,'SC','South Carolina'),
(49,'SD','South Dakota'),
(50,'TN','Tennessee'),
(51,'TX','Texas'),
(52,'UT','Utah'),
(53,'VT','Vermont'),
(54,'VI','Virgin Islands'),
(55,'VA','Virginia'),
(56,'WA','Washington'),
(57,'WV','West Virginia'),
(58,'WI','Wisconsin'),
(59,'WY','Wyoming'),
(60,'AE','Armed Forces Africa'),
(61,'AA','Armed Forces Americas (except Canada)'),
(62,'AE','Armed Forces Canada'),
(63,'AE','Armed Forces Europe'),
(64,'AE','Armed Forces Middle East'),
(65,'AP','Armed Forces Pacific');

Discussion (0)