Technology

SQL: Full Name Lookup for States and Provinces for the US and Canada

While working on a Google Mapping project this weekend, I needed to make a table in MySQL that translated the 2-digit country and state abbreviations to a full name. I’m sure this is something that is needed quite a bit, so I’ve put the SQL file up on my server for download.

Here’s the SQL statement for State and Province Abbreviations for the United States and Canada. I’ve also added an Index on both the country and state ids to improve performance.

CREATE TABLE `states` (
`COUNTRYID` varchar(2) NOT NULL,
`STATEID` varchar(2) NOT NULL,
`FULLNAME` varchar(30) NOT NULL,
KEY `country` (`COUNTRYID`),
KEY `id` (`STATEID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='State and Province Full Name Lookup for US and Canada';

INSERT INTO `states` (`COUNTRYID`, `STATEID`, `FULLNAME`) VALUES
('CA', 'AB', 'Alberta'),
('CA', 'BC', 'British Columbia'),
('CA', 'MB', 'Manitoba'),
('CA', 'NB', 'New Brunswick'),
('CA', 'NL', 'Newfoundland and Labrador'),
('CA', 'NS', 'Nova Scotia'),
('CA', 'NT', 'Northwest Territories'),
('CA', 'NU', 'Nunavut'),
('CA', 'ON', 'Ontario'),
('CA', 'PE', 'Prince Edward Island'),
('CA', 'QC', 'Quebec'),
('CA', 'SK', 'Saskatachewan'),
('CA', 'YT', 'Yukon'),
('US', 'AK', 'Alaska'),
('US', 'AL', 'Alabama'),
('US', 'AR', 'Arkansas'),
('US', 'AZ', 'Arizona'),
('US', 'CA', 'California'),
('US', 'CO', 'Colorado'),
('US', 'CT', 'Connecticut'),
('US', 'DC', 'District of Columbia'),
('US', 'DE', 'Delaware'),
('US', 'FL', 'Florida'),
('US', 'GA', 'Georgia'),
('US', 'HI', 'Hawaii'),
('US', 'IA', 'Iowa'),
('US', 'ID', 'Idaho'),
('US', 'IL', 'Illinois'),
('US', 'IN', 'Indiana'),
('US', 'KS', 'Kansas'),
('US', 'KY', 'Kentucky'),
('US', 'LA', 'Louisiana'),
('US', 'MA', 'Massachusetts'),
('US', 'MD', 'Maryland'),
('US', 'ME', 'Maine'),
('US', 'MI', 'Michigan'),
('US', 'MN', 'Minnesota'),
('US', 'MO', 'Missouri'),
('US', 'MS', 'Mississippi'),
('US', 'MT', 'Montana'),
('US', 'NC', 'North Carolina'),
('US', 'ND', 'North Dakota'),
('US', 'NE', 'Nebraska'),
('US', 'NH', 'New Hampshire'),
('US', 'NJ', 'New Jersey'),
('US', 'NM', 'New Mexico'),
('US', 'NV', 'Nevada'),
('US', 'NY', 'New York'),
('US', 'OH', 'Ohio'),
('US', 'OK', 'Oklahoma'),
('US', 'OR', 'Oregon'),
('US', 'PA', 'Pennsylvania'),
('US', 'RI', 'Rhode Island'),
('US', 'SC', 'South Carolina'),
('US', 'SD', 'South Dakota'),
('US', 'TN', 'Tennessee'),
('US', 'TX', 'Texas'),
('US', 'UT', 'Utah'),
('US', 'VA', 'Virginia'),
('US', 'VI', 'Virgin Islands'),
('US', 'VT', 'Vermont'),
('US', 'WA', 'Washington'),
('US', 'WI', 'Wisconsin'),
('US', 'WV', 'West Virginia'),
('US', 'WY', 'Wyoming');

Hat tip to Brian who had a nice CSV I was able to work from.

Leave a Reply