Posted By

cbrant on 06/10/08


Tagged

mysql states table list us


Versions (?)

Who likes this?

5 people have marked this snippet as a favorite

banzaiman
cbrant
walshsr
mattchuk
lunuledesign


MySQL Table - US States


 / Published in: SQL
 

URL: http://27.org/isocountrylist/

This will create and then populate a MySQL table with a list of the names and USPS abbreviations for US states and possessions in existence.

  1. # usps_states_list.sql
  2. #
  3. # This will create and then populate a MySQL table with a list of the names and
  4. # USPS abbreviations for US states and possessions in existence as of the date
  5. # below.
  6. #
  7. # Usage:
  8. # mysql -u username -ppassword database_name < usps_states_list.sql
  9. #
  10. # For updates to this file, see http://27.org/isocountrylist/
  11. # For more about USPS state abbreviations, see http://www.usps.com/ncsc/lookups/usps_abbreviations.html
  12. #
  13. # Wm. Rhodes <iso_country_list@27.org>
  14. # 1/1/03
  15. #
  16.  
  17. CREATE TABLE IF NOT EXISTS states (
  18. id INT NOT NULL AUTO_INCREMENT,
  19. name CHAR(40) NOT NULL,
  20. abbrev CHAR(2) NOT NULL,
  21. PRIMARY KEY (id)
  22. );
  23.  
  24. INSERT INTO states VALUES (NULL, 'Alaska', 'AK');
  25. INSERT INTO states VALUES (NULL, 'Alabama', 'AL');
  26. INSERT INTO states VALUES (NULL, 'American Samoa', 'AS');
  27. INSERT INTO states VALUES (NULL, 'Arizona', 'AZ');
  28. INSERT INTO states VALUES (NULL, 'Arkansas', 'AR');
  29. INSERT INTO states VALUES (NULL, 'California', 'CA');
  30. INSERT INTO states VALUES (NULL, 'Colorado', 'CO');
  31. INSERT INTO states VALUES (NULL, 'Connecticut', 'CT');
  32. INSERT INTO states VALUES (NULL, 'Delaware', 'DE');
  33. INSERT INTO states VALUES (NULL, 'District of Columbia', 'DC');
  34. INSERT INTO states VALUES (NULL, 'Federated States of Micronesia', 'FM');
  35. INSERT INTO states VALUES (NULL, 'Florida', 'FL');
  36. INSERT INTO states VALUES (NULL, 'Georgia', 'GA');
  37. INSERT INTO states VALUES (NULL, 'Guam', 'GU');
  38. INSERT INTO states VALUES (NULL, 'Hawaii', 'HI');
  39. INSERT INTO states VALUES (NULL, 'Idaho', 'ID');
  40. INSERT INTO states VALUES (NULL, 'Illinois', 'IL');
  41. INSERT INTO states VALUES (NULL, 'Indiana', 'IN');
  42. INSERT INTO states VALUES (NULL, 'Iowa', 'IA');
  43. INSERT INTO states VALUES (NULL, 'Kansas', 'KS');
  44. INSERT INTO states VALUES (NULL, 'Kentucky', 'KY');
  45. INSERT INTO states VALUES (NULL, 'Louisiana', 'LA');
  46. INSERT INTO states VALUES (NULL, 'Maine', 'ME');
  47. INSERT INTO states VALUES (NULL, 'Marshall Islands', 'MH');
  48. INSERT INTO states VALUES (NULL, 'Maryland', 'MD');
  49. INSERT INTO states VALUES (NULL, 'Massachusetts', 'MA');
  50. INSERT INTO states VALUES (NULL, 'Michigan', 'MI');
  51. INSERT INTO states VALUES (NULL, 'Minnesota', 'MN');
  52. INSERT INTO states VALUES (NULL, 'Mississippi', 'MS');
  53. INSERT INTO states VALUES (NULL, 'Missouri', 'MO');
  54. INSERT INTO states VALUES (NULL, 'Montana', 'MT');
  55. INSERT INTO states VALUES (NULL, 'Nebraska', 'NE');
  56. INSERT INTO states VALUES (NULL, 'Nevada', 'NV');
  57. INSERT INTO states VALUES (NULL, 'New Hampshire', 'NH');
  58. INSERT INTO states VALUES (NULL, 'New Jersey', 'NJ');
  59. INSERT INTO states VALUES (NULL, 'New Mexico', 'NM');
  60. INSERT INTO states VALUES (NULL, 'New York', 'NY');
  61. INSERT INTO states VALUES (NULL, 'North Carolina', 'NC');
  62. INSERT INTO states VALUES (NULL, 'North Dakota', 'ND');
  63. INSERT INTO states VALUES (NULL, 'Northern Mariana Islands', 'MP');
  64. INSERT INTO states VALUES (NULL, 'Ohio', 'OH');
  65. INSERT INTO states VALUES (NULL, 'Oklahoma', 'OK');
  66. INSERT INTO states VALUES (NULL, 'Oregon', 'OR');
  67. INSERT INTO states VALUES (NULL, 'Palau', 'PW');
  68. INSERT INTO states VALUES (NULL, 'Pennsylvania', 'PA');
  69. INSERT INTO states VALUES (NULL, 'Puerto Rico', 'PR');
  70. INSERT INTO states VALUES (NULL, 'Rhode Island', 'RI');
  71. INSERT INTO states VALUES (NULL, 'South Carolina', 'SC');
  72. INSERT INTO states VALUES (NULL, 'South Dakota', 'SD');
  73. INSERT INTO states VALUES (NULL, 'Tennessee', 'TN');
  74. INSERT INTO states VALUES (NULL, 'Texas', 'TX');
  75. INSERT INTO states VALUES (NULL, 'Utah', 'UT');
  76. INSERT INTO states VALUES (NULL, 'Vermont', 'VT');
  77. INSERT INTO states VALUES (NULL, 'Virgin Islands', 'VI');
  78. INSERT INTO states VALUES (NULL, 'Virginia', 'VA');
  79. INSERT INTO states VALUES (NULL, 'Washington', 'WA');
  80. INSERT INTO states VALUES (NULL, 'West Virginia', 'WV');
  81. INSERT INTO states VALUES (NULL, 'Wisconsin', 'WI');
  82. INSERT INTO states VALUES (NULL, 'Wyoming', 'WY');
  83. INSERT INTO states VALUES (NULL, 'Armed Forces Africa', 'AE');
  84. INSERT INTO states VALUES (NULL, 'Armed Forces Americas (except Canada)', 'AA');
  85. INSERT INTO states VALUES (NULL, 'Armed Forces Canada', 'AE');
  86. INSERT INTO states VALUES (NULL, 'Armed Forces Europe', 'AE');
  87. INSERT INTO states VALUES (NULL, 'Armed Forces Middle East', 'AE');
  88. INSERT INTO states VALUES (NULL, 'Armed Forces Pacific', 'AP');

Report this snippet  

You need to login to post a comment.