Changing State Abbreviations to Full Name

We have some data that provides state abbreviations (i.e. AL, DC, NM, VA, etc.); however, we need to be able to transition those values to the full names of the states, or numbers (i.e. Alabama would be 1, Alaska 2, etc.). Is there any way to do this within a T-SQL query?
Who is Participating?
Kyle AbrahamsSenior .Net DeveloperCommented:
the easiest way would be to create a new table in your database.

id, abbreviation, state_name

you can then right functions to convert 1 to any other.

eg input id output abbreviation or state

If doing right from a select you could also just join the table.
Kevin CrossChief Technology OfficerCommented:
Create a table of states with the following columns:


If using SQL Server 2005+, you can always resort the order by using row_number() statement ordered by name ascending.  If not, you will have to resort order values on insert of new state name.

Then you can use the table of states to join to your data where abbreviations equal and then display either number or name.
Chris LuttrellSenior Database ArchitectCommented:
If you are wanting a list of all the states and their abbreviations you can get it off the USPS web site at
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.