MySQL String to Replace Postal Codes With State Names

I recently had to modify a mySql database to replace postal codes (i.e., "FL") with complete state names (i.e., "Florida"). I searched all over and did not find a ready-made string to accomplish this.

I am sure somewhere that someone will find this useful.

Simply replace "dbo_vwPRVDR" with your table name, and "State" with the column name, and you’ll be in business.

update dbo_vwPRVDR set State = replace(State,'AL','Alabama');
update dbo_vwPRVDR set State = replace(State,'AK','Alaska');
update dbo_vwPRVDR set State = replace(State,'AZ','Arizona');
update dbo_vwPRVDR set State = replace(State,'AR','Arkansas');
update dbo_vwPRVDR set State = replace(State,'CA','California');
update dbo_vwPRVDR set State = replace(State,'CO','Colorado');
update dbo_vwPRVDR set State = replace(State,'CT','Connecticut');
update dbo_vwPRVDR set State = replace(State,'DC','Washington DC');
update dbo_vwPRVDR set State = replace(State,'DE','Delaware');
update dbo_vwPRVDR set State = replace(State,'FL','Florida');
update dbo_vwPRVDR set State = replace(State,'GA','Georgia');
update dbo_vwPRVDR set State = replace(State,'HI','Hawaii');
update dbo_vwPRVDR set State = replace(State,'IA','Iowa');
update dbo_vwPRVDR set State = replace(State,'ID','Idaho');
update dbo_vwPRVDR set State = replace(State,'IL','Illinois');
update dbo_vwPRVDR set State = replace(State,'IN','Indiana');
update dbo_vwPRVDR set State = replace(State,'KS','Kansas');
update dbo_vwPRVDR set State = replace(State,'KY','Kentucky');
update dbo_vwPRVDR set State = replace(State,'LA','Louisiana');
update dbo_vwPRVDR set State = replace(State,'MA','Massachusetts');
update dbo_vwPRVDR set State = replace(State,'MD','Maryland');
update dbo_vwPRVDR set State = replace(State,'ME','Maine');
update dbo_vwPRVDR set State = replace(State,'MI','Michigan');
update dbo_vwPRVDR set State = replace(State,'MN','Minnesota');
update dbo_vwPRVDR set State = replace(State,'MO','Missouri');
update dbo_vwPRVDR set State = replace(State,'MS','Mississippi');
update dbo_vwPRVDR set State = replace(State,'NC','North Carolina');
update dbo_vwPRVDR set State = replace(State,'ND','North Dakota');
update dbo_vwPRVDR set State = replace(State,'NE','Nebraska');
update dbo_vwPRVDR set State = replace(State,'NH','New Hampshire');
update dbo_vwPRVDR set State = replace(State,'NJ','New Jersey');
update dbo_vwPRVDR set State = replace(State,'NM','New Mexico');
update dbo_vwPRVDR set State = replace(State,'NV','Nevada');
update dbo_vwPRVDR set State = replace(State,'NY','New York');
update dbo_vwPRVDR set State = replace(State,'OH','Ohio');
update dbo_vwPRVDR set State = replace(State,'OK','Oklahoma');
update dbo_vwPRVDR set State = replace(State,'OR','Oregon');
update dbo_vwPRVDR set State = replace(State,'PA','Pennsylvania');
update dbo_vwPRVDR set State = replace(State,'PR','Puerto Rico');
update dbo_vwPRVDR set State = replace(State,'RI','Rhode Island');
update dbo_vwPRVDR set State = replace(State,'SC','South Carolina');
update dbo_vwPRVDR set State = replace(State,'SD','South Dakota');
update dbo_vwPRVDR set State = replace(State,'TN','Tennessee');
update dbo_vwPRVDR set State = replace(State,'TX','Texas');
update dbo_vwPRVDR set State = replace(State,'UT','Utah');
update dbo_vwPRVDR set State = replace(State,'VA','Virginia');
update dbo_vwPRVDR set State = replace(State,'VT','Vermont');
update dbo_vwPRVDR set State = replace(State,'WA','Washington');
update dbo_vwPRVDR set State = replace(State,'WI','Wisconsin');
update dbo_vwPRVDR set State = replace(State,'WV','West Virginia');
update dbo_vwPRVDR set State = replace(State,'WY','Wyoming');

Bookmark It!