SQL Select Convert State Name To Abbreviation

Question

In a SQL select statement, how to convert a full state name to state abbreviation (e.g. New York to NY)? I'd like to do this without joins if possible. What would the regexp_replace look like?

select regexp_replace(table.state, 'New York', 'NY', 'g') as state
Can this approach be done en mass for all states?

For reference list of states names and abbreviations: https://gist.github.com/esfand/9443427.

Answer 1

Here it is in raw WHEN/THEN form if needed, along with Canadian provinces:

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