advanced code snippet search

Posted By

aeboi80 on 07/06/10


Tagged

stateprovinceuscanadauk


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

jackkeller
kubaitis
megadeth_tom


State/Province For: United States, Canada, and UK

 / Published in: MySQL
 
Expand | Embed | Plain Text 
  1. SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
  2.  
  3.  
  4. -- Creation Date: July 05, 2010
  5. --
  6. -- Created By: Chad Warren
  7. -- Website: http://www.iamchad.com
  8. --
  9. -- Create table to store the full state name and abbreviation for all of the states and provinces within the United States of America, Canada and the United Kingdom
  10. --
  11.  
  12. -- --------------------------------------------------------
  13.  
  14. --
  15. -- Table structure for table `state`
  16. --
  17.  
  18. CREATE TABLE IF NOT EXISTS `state` (
  19.   `state_id` INT(2) NOT NULL AUTO_INCREMENT,
  20.   `state_name` VARCHAR(32) NOT NULL COMMENT 'State name with first letter capital',
  21.   `state_abbr` VARCHAR(8) DEFAULT NULL COMMENT 'Optional state abbreviation (US is 2 capital letters)',
  22.   PRIMARY KEY (`state_id`)
  23. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=183 ;
  24.  
  25. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','AK','Alaska');
  26. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','AL','Alabama');
  27. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','AR','Arkansas');
  28. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','AZ','Arizona');
  29. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','CA','California');
  30. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','CO','Colorado');
  31. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','CT','Connecticut');
  32. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','DE','Delaware');
  33. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','DC','District of Columbia');
  34. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','FL','Florida');
  35. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','GA','Georgia');
  36. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','HI','Hawaii');
  37. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','IA','Iowa');
  38. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','ID','Idaho');
  39. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','IL','Illinois');
  40. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','IN','Indiana');
  41. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','KS','Kansas');
  42. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','KY','Kentucky');
  43. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','LA','Louisiana');
  44. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','MA','Massachusetts');
  45. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','MD','Maryland');
  46. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','ME','Maine');
  47. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','MI','Michigan');
  48. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','MN','Minnesota');
  49. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','MO','Missouri');
  50. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','MS','Mississippi');
  51. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','MT','Montana');
  52. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NC','North Carolina');
  53. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','ND','North Dakota');
  54. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NE','Nebraska');
  55. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NH','New Hampshire');
  56. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NJ','New Jersey');
  57. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NM','New Mexico');
  58. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NV','Nevada');
  59. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NY','New York');
  60. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','OH','Ohio');
  61. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','OK','Oklahoma');
  62. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','OR','Oregon');
  63. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','PA','Pennsylvania');
  64. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','RI','Rhode Island');
  65. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','SC','South Carolina');
  66. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','SD','South Dakota');
  67. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','TN','Tennessee');
  68. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','TX','Texas');
  69. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','UT','Utah');
  70. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','VA','Virginia');
  71. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','VT','Vermont');
  72. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','WA','Washington');
  73. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','WI','Wisconsin');
  74. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','WV','West Virginia');
  75. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','WY','Wyoming');
  76. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','AB','Alberta');
  77. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','BC','British Columbia');
  78. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','MB','Manitoba');
  79. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NB','New Brunswick');
  80. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NL','Newfoundland & Labrador');
  81. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NS','Nova Scotia');
  82. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NT','Northwest Territories');
  83. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','NU','Nunavut');
  84. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','ON','Ontario');
  85. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','PE','Prince Edward Island');
  86. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','QC','Quebec');
  87. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','SK','Saskatchewan');
  88. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','YT','Yukon Territory');
  89. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','England');
  90. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Avon');
  91. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Bedfordshire');
  92. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Berkshire');
  93. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Bristol');
  94. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Buckinghamshire');
  95. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Cambridgeshire');
  96. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Cheshire');
  97. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Cleveland');
  98. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Cornwall');
  99. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Cumbria');
  100. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Derbyshire');
  101. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Devon');
  102. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Dorset');
  103. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Durham');
  104. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','East Riding of Yorkshire');
  105. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','East Sussex');
  106. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Essex');
  107. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Gloucestershire');
  108. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Greater Manchester');
  109. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Hampshire');
  110. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Herefordshire');
  111. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Hertfordshire');
  112. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Humberside');
  113. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Isle of Wight');
  114. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Isles of Scilly');
  115. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Kent');
  116. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Lancashire');
  117. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Leicestershire');
  118. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Lincolnshire');
  119. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','London');
  120. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Merseyside');
  121. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Middlesex');
  122. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Norfolk');
  123. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','North Yorkshire');
  124. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Northamptonshire');
  125. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Northumberland');
  126. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Nottinghamshire');
  127. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Oxfordshire');
  128. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Rutland');
  129. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Shropshire');
  130. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Somerset');
  131. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','South Yorkshire');
  132. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Staffordshire');
  133. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Suffolk');
  134. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Surrey');
  135. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Tyne and Wear');
  136. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Warwickshire');
  137. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','West Midlands');
  138. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','West Sussex');
  139. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','West Yorkshire');
  140. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Wiltshire');
  141. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Worcestershire');
  142. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Northern Ireland');
  143. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Antrim');
  144. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Armagh');
  145. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Down');
  146. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Fermanagh');
  147. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Londonderry');
  148. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Tyrone');
  149. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Scotland');
  150. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Aberdeen City');
  151. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Aberdeenshire');
  152. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Angus');
  153. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Argyll and Bute');
  154. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Borders');
  155. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Clackmannan');
  156. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Dumfries and Galloway');
  157. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','East Ayrshire');
  158. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','East Dunbartonshire');
  159. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','East Lothian');
  160. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','East Renfrewshire');
  161. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Edinburgh City');
  162. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Falkirk');
  163. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Fife');
  164. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Glasgow');
  165. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Highland');
  166. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Inverclyde');
  167. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Midlothian');
  168. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Moray');
  169. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','North Ayrshire');
  170. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','North Lanarkshire');
  171. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Orkney');
  172. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Perthshire and Kinross');
  173. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Renfrewshire');
  174. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Roxburghshire');
  175. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Shetland');
  176. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','South Ayrshire');
  177. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','South Lanarkshire');
  178. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Stirling');
  179. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','West Dunbartonshire');
  180. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','West Lothian');
  181. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Western Isles');
  182. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Blaenau Gwent');
  183. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Bridgend');
  184. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Caerphilly');
  185. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Cardiff');
  186. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Carmarthenshire');
  187. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Ceredigion');
  188. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Conwy');
  189. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Denbighshire');
  190. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Flintshire');
  191. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Gwynedd');
  192. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Isle of Anglesey');
  193. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Merthyr Tydfil');
  194. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Monmouthshire');
  195. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Neath Port Talbot');
  196. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Newport');
  197. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Pembrokeshire');
  198. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Powys');
  199. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Rhondda Cynon Taff');
  200. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Swansea');
  201. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Torfaen');
  202. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','The Vale of Glamorgan');
  203. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Wrexham');
  204. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','UK Offshore Dependencies');
  205. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Channel Islands');
  206. INSERT INTO state(state_id,state_abbr,state_name) VALUES('NULL','  ','Isle of Man');

Report this snippet  

You need to login to post a comment.