SQL: Cuardach Iomlán Ainm do Stáit agus Cúige do na SA agus Ceanada

Meiriceá Thuaidh

Agus mé ag obair ar thionscadal Google Mapping an deireadh seachtaine seo, bhí orm tábla a dhéanamh isteach 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 TÁBLA `stáit` (` COUNTRYID` varchar (2) NOT NULL, `STATEID` varchar (2) NOT NULL,` FULLNAME` varchar (30) NOT NULL, KEY `country` (` COUNTRYID`), KEY `id` (`STATEID`)) INNEALT = MyISAM DEFAULT CHARSET = utf8 COMMENT = 'Stát agus Cúige Cuardach Ainm Iomlán do SAM agus Ceanada';
CUIR ISTEACH I LUACHANNA `stáit` (` COUNTRYID`, `STATEID`,` FULLNAME`) ('CA', 'AB', 'Alberta'), ('CA', 'BC', 'British Columbia'), (' CA ',' MB ',' Manitoba '), (' CA ',' NB ',' New Brunswick '), (' CA ',' NL ',' Talamh an Éisc agus Labradar '), (' CA ',' NS ',' Albain Nua '), (' CA ',' NT ',' Críocha an Iarthuaiscirt '), (' CA ',' NU ',' Nunavut '), (' CA ',' ON ',' Ontario ') , ('CA', 'PE', 'Prince Edward Island'), ('CA', 'QC', 'Québec'), ('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', 'Haváí'), ('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', ‘Mar. yland '), (' US ',' ME ',' Maine '), (' US ',' MI ',' Michigan '), (' US ',' MN ',' Minnesota '), (' US ' , 'MO', 'Missouri'), ('US', 'MS', 'Mississippi'), ('US', 'MT', 'Montana'), ('US', 'NC', 'North Carolina '), (' US ',' ND ',' Dakota Thuaidh '), (' US ',' NE ',' Nebraska '), (' US ',' NH ',' New Hampshire '), (' US ',' NJ ',' New Jersey '), (' US ',' NM ',' New Mexico '), (' US ',' NV ',' Nevada '), (' US ',' NY ', 'Nua Eabhrac'), ('US', 'OH', 'Ohio'), ('US', 'OK', 'Oklahoma'), ('US', 'OR', 'Oregon'), (' US ',' PA ',' Pennsylvania '), (' US ',' RI ',' Rhode Island '), (' US ',' SC ',' Carolina Theas '), (' 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');

Leid hata chuig Brian a raibh CSV deas aige Bhí mé in ann obair a dhéanamh ó.

Cad é do bharúil?

Úsáideann an suíomh seo Akismet chun spam a laghdú. Foghlaim conas a dhéantar próiseáil ar do chuid tráchta.