通过postgresql，您可以使用json

select '{"Alabama": "AL", "Alaska": "AK"}'::json->'Alabama'

也可以使用列引用而不是字符串文本

select 
'{"Alabama": "AL", "Alaska": "AK"}'::json->example.state 
from 
(values ('Alabama')) example(state)

如有需要，这里有原始的when/then格式，以及加拿大各省：

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

正如评论所说，需要一个连接。下面是我最后做的。如果有更好的办法，请告诉我。

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

以及 select (select states.abbr from states where name = state_name)