Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate state code abbreviations handling by the CASE function and a lookup table.

Note that using the case function means hard-wiring data in T-SQL code, only recommended for adhoc queries. For permanent solution lookup table is the right choice.

-- SQL CASE function for abbreviations - SQL lookup table for abbreviations
USE AdventureWorks;
GO
-- SQL CASE function for abbreviations
-- The CASE function can be used the reverse way also: StateName à Code
DECLARE @StateCode char(2)
SET @StateCode = 'TX'
SELECT 'State name from state code' =
CASE
WHEN @StateCode = 'AK' THEN 'ALASKA'
WHEN @StateCode = 'AL' THEN 'ALABAMA'
WHEN @StateCode = 'AR' THEN 'ARKANSAS'
WHEN @StateCode = 'AS' THEN 'AMERICAN SAMOA'
WHEN @StateCode = 'AZ' THEN 'ARIZONA '
WHEN @StateCode = 'CA' THEN 'CALIFORNIA '
WHEN @StateCode = 'CO' THEN 'COLORADO '
WHEN @StateCode = 'CT' THEN 'CONNECTICUT'
WHEN @StateCode = 'DC' THEN 'DISTRICT OF COLUMBIA'
WHEN @StateCode = 'DE' THEN 'DELAWARE'
WHEN @StateCode = 'FL' THEN 'FLORIDA'
WHEN @StateCode = 'FM' THEN 'FEDERATED STATES OF MICRONESIA'
WHEN @StateCode = 'GA' THEN 'GEORGIA'
WHEN @StateCode = 'GU' THEN 'GUAM '
WHEN @StateCode = 'HI' THEN 'HAWAII'
WHEN @StateCode = 'IA' THEN 'IOWA'
WHEN @StateCode = 'ID' THEN 'IDAHO'
WHEN @StateCode = 'IL' THEN 'ILLINOIS'
WHEN @StateCode = 'IN' THEN 'INDIANA'
WHEN @StateCode = 'KS' THEN 'KANSAS'
WHEN @StateCode = 'KY' THEN 'KENTUCKY'
WHEN @StateCode = 'LA' THEN 'LOUISIANA'
WHEN @StateCode = 'MA' THEN 'MASSACHUSETTS'
WHEN @StateCode = 'MD' THEN 'MARYLAND'
WHEN @StateCode = 'ME' THEN 'MAINE'
WHEN @StateCode = 'MH' THEN 'MARSHALL ISLANDS'
WHEN @StateCode = 'MI' THEN 'MICHIGAN'
WHEN @StateCode = 'MN' THEN 'MINNESOTA'
WHEN @StateCode = 'MO' THEN 'MISSOURI'
WHEN @StateCode = 'MP' THEN 'NORTHERN MARIANA ISLANDS'
WHEN @StateCode = 'MS' THEN 'MISSISSIPPI'
WHEN @StateCode = 'MT' THEN 'MONTANA'
WHEN @StateCode = 'NC' THEN 'NORTH CAROLINA'
WHEN @StateCode = 'ND' THEN 'NORTH DAKOTA'
WHEN @StateCode = 'NE' THEN 'NEBRASKA'
WHEN @StateCode = 'NH' THEN 'NEW HAMPSHIRE'
WHEN @StateCode = 'NJ' THEN 'NEW JERSEY'
WHEN @StateCode = 'NM' THEN 'NEW MEXICO'
WHEN @StateCode = 'NV' THEN 'NEVADA'
WHEN @StateCode = 'NY' THEN 'NEW YORK'
WHEN @StateCode = 'OH' THEN 'OHIO'
WHEN @StateCode = 'OK' THEN 'OKLAHOMA'
WHEN @StateCode = 'OR' THEN 'OREGON'
WHEN @StateCode = 'PA' THEN 'PENNSYLVANIA'
WHEN @StateCode = 'PR' THEN 'PUERTO RICO'
WHEN @StateCode = 'RI' THEN 'RHODE ISLAND'
WHEN @StateCode = 'SC' THEN 'SOUTH CAROLINA'
WHEN @StateCode = 'SD' THEN 'SOUTH DAKOTA'
WHEN @StateCode = 'TN' THEN 'TENNESSEE'
WHEN @StateCode = 'TX' THEN 'TEXAS'
WHEN @StateCode = 'UT' THEN 'UTAH'
WHEN @StateCode = 'VA' THEN 'VIRGINIA '
WHEN @StateCode = 'VI' THEN 'VIRGIN ISLANDS'
WHEN @StateCode = 'VT' THEN 'VERMONT'
WHEN @StateCode = 'WA' THEN 'WASHINGTON'
WHEN @StateCode = 'WI' THEN 'WISCONSIN'
WHEN @StateCode = 'WV' THEN 'WEST VIRGINIA'
WHEN @StateCode = 'WY' THEN 'WYOMING'
ELSE '' END
GO
/* Result
 
State name from state code
TEXAS
*/
 
-- SQL create lookup table
CREATE TABLE dbo.StateCode (
  StateCodeID INT    IDENTITY ( 1 , 1 ),
  State       VARCHAR(30),
  Code        CHAR(2)    PRIMARY KEY);
GO
 
-- SQL populate lookup table
INSERT INTO dbo.StateCode VALUES('ALABAMA','AL');
INSERT INTO dbo.StateCode VALUES('ALASKA','AK');
INSERT INTO dbo.StateCode VALUES('AMERICAN SAMOA','AS');
INSERT INTO dbo.StateCode VALUES('ARIZONA ','AZ');
INSERT INTO dbo.StateCode VALUES('ARKANSAS','AR');
INSERT INTO dbo.StateCode VALUES('CALIFORNIA ','CA');
INSERT INTO dbo.StateCode VALUES('COLORADO ','CO');
INSERT INTO dbo.StateCode VALUES('CONNECTICUT','CT');
INSERT INTO dbo.StateCode VALUES('DELAWARE','DE');
INSERT INTO dbo.StateCode VALUES('DISTRICT OF COLUMBIA','DC');
INSERT INTO dbo.StateCode VALUES('FEDERATED STATES OF MICRONESIA','FM');
INSERT INTO dbo.StateCode VALUES('FLORIDA','FL');
INSERT INTO dbo.StateCode VALUES('GEORGIA','GA');
INSERT INTO dbo.StateCode VALUES('GUAM ','GU');
INSERT INTO dbo.StateCode VALUES('HAWAII','HI');
INSERT INTO dbo.StateCode VALUES('IDAHO','ID');
INSERT INTO dbo.StateCode VALUES('ILLINOIS','IL');
INSERT INTO dbo.StateCode VALUES('INDIANA','IN');
INSERT INTO dbo.StateCode VALUES('IOWA','IA');
INSERT INTO dbo.StateCode VALUES('KANSAS','KS');
INSERT INTO dbo.StateCode VALUES('KENTUCKY','KY');
INSERT INTO dbo.StateCode VALUES('LOUISIANA','LA');
INSERT INTO dbo.StateCode VALUES('MAINE','ME');
INSERT INTO dbo.StateCode VALUES('MARSHALL ISLANDS','MH');
INSERT INTO dbo.StateCode VALUES('MARYLAND','MD');
INSERT INTO dbo.StateCode VALUES('MASSACHUSETTS','MA');
INSERT INTO dbo.StateCode VALUES('MICHIGAN','MI');
INSERT INTO dbo.StateCode VALUES('MINNESOTA','MN');
INSERT INTO dbo.StateCode VALUES('MISSISSIPPI','MS');
INSERT INTO dbo.StateCode VALUES('MISSOURI','MO');
INSERT INTO dbo.StateCode VALUES('MONTANA','MT');
INSERT INTO dbo.StateCode VALUES('NEBRASKA','NE');
INSERT INTO dbo.StateCode VALUES('NEVADA','NV');
INSERT INTO dbo.StateCode VALUES('NEW HAMPSHIRE','NH');
INSERT INTO dbo.StateCode VALUES('NEW JERSEY','NJ');
INSERT INTO dbo.StateCode VALUES('NEW MEXICO','NM');
INSERT INTO dbo.StateCode VALUES('NEW YORK','NY');
INSERT INTO dbo.StateCode VALUES('NORTH CAROLINA','NC');
INSERT INTO dbo.StateCode VALUES('NORTH DAKOTA','ND');
INSERT INTO dbo.StateCode VALUES('NORTHERN MARIANA ISLANDS','MP');
INSERT INTO dbo.StateCode VALUES('OHIO','OH');
INSERT INTO dbo.StateCode VALUES('OKLAHOMA','OK');
INSERT INTO dbo.StateCode VALUES('OREGON','OR');
INSERT INTO dbo.StateCode VALUES('PENNSYLVANIA','PA');
INSERT INTO dbo.StateCode VALUES('PUERTO RICO','PR');
INSERT INTO dbo.StateCode VALUES('RHODE ISLAND','RI');
INSERT INTO dbo.StateCode VALUES('SOUTH CAROLINA','SC');
INSERT INTO dbo.StateCode VALUES('SOUTH DAKOTA','SD');
INSERT INTO dbo.StateCode VALUES('TENNESSEE','TN');
INSERT INTO dbo.StateCode VALUES('TEXAS','TX');
INSERT INTO dbo.StateCode VALUES('UTAH','UT');
INSERT INTO dbo.StateCode VALUES('VERMONT','VT');
INSERT INTO dbo.StateCode VALUES('VIRGIN ISLANDS','VI');
INSERT INTO dbo.StateCode VALUES('VIRGINIA ','VA');
INSERT INTO dbo.StateCode VALUES('WASHINGTON','WA');
INSERT INTO dbo.StateCode VALUES('WEST VIRGINIA','WV');
INSERT INTO dbo.StateCode VALUES('WISCONSIN','WI');
INSERT INTO dbo.StateCode VALUES('WYOMING','WY');
go
 
SELECT *
FROM   dbo.StateCode
ORDER BY StateCodeID
GO
/* Results
StateCodeID State                               Code
1           ALABAMA                             AL
2           ALASKA                              AK
3           AMERICAN SAMOA                      AS
4           ARIZONA                             AZ
5           ARKANSAS                            AR
6           CALIFORNIA                          CA
7           COLORADO                            CO
8           CONNECTICUT                         CT
9           DELAWARE                            DE
10          DISTRICT OF COLUMBIA                DC
11          FEDERATED STATES OF MICRONESIA      FM
12          FLORIDA                             FL
13          GEORGIA                             GA
14          GUAM                                GU
15          HAWAII                              HI
16          IDAHO                               ID
17          ILLINOIS                            IL
18          INDIANA                             IN
19          IOWA                                IA
20          KANSAS                              KS
21          KENTUCKY                            KY
22          LOUISIANA                           LA
23          MAINE                               ME
24          MARSHALL ISLANDS                    MH
25          MARYLAND                            MD
26          MASSACHUSETTS                       MA
27          MICHIGAN                            MI
28          MINNESOTA                           MN
29          MISSISSIPPI                         MS
30          MISSOURI                            MO
31          MONTANA                             MT
32          NEBRASKA                            NE
33          NEVADA                              NV
34          NEW HAMPSHIRE                       NH
35          NEW JERSEY                          NJ
36          NEW MEXICO                          NM
37          NEW YORK                            NY
38          NORTH CAROLINA                      NC
39          NORTH DAKOTA                        ND
40          NORTHERN MARIANA ISLANDS            MP
41          OHIO                                OH
42          OKLAHOMA                            OK
43          OREGON                              OR
44          PENNSYLVANIA                        PA
45          PUERTO RICO                         PR
46          RHODE ISLAND                        RI
47          SOUTH CAROLINA                      SC
48          SOUTH DAKOTA                        SD
49          TENNESSEE                           TN
50          TEXAS                               TX
51          UTAH                                UT
52          VERMONT                             VT
53          VIRGIN ISLANDS                      VI
54          VIRGINIA                            VA
55          WASHINGTON                          WA
56          WEST VIRGINIA                       WV
57          WISCONSIN                           WI
58          WYOMING                             WY
*/
 
-- Generate sql script for the CASE function above
-- SQL script generator
SELECT 'WHEN @StateCode = '''+Code+ ''' THEN '''+ State+''''
FROM StateCode
/* Partial results
 
WHEN @StateCode = 'HI' THEN 'HAWAII'
WHEN @StateCode = 'IA' THEN 'IOWA'
WHEN @StateCode = 'ID' THEN 'IDAHO'
*/
 
-- Cleanup
DROP TABLE dbo.StateCode

GO