Changing State Abbreviations to Full Name

Posted on 2009-04-30
Last Modified: 2012-05-06
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?
Question by:rdracer58
    LVL 39

    Accepted Solution

    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.
    LVL 59

    Assisted Solution

    by:Kevin Cross
    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.
    LVL 26

    Assisted Solution

    by:Chris Luttrell
    If you are wanting a list of all the states and their abbreviations you can get it off the USPS web site at

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now