# Standardizing US State Name Data to State Code

**URL:** <https://community.omni.co/t/standardizing-us-state-name-data-to-state-code/288>\
**Category:** Modeling\
**Tags:** modeling\
**Created:** [March 27, 2025, 9:55pm UTC](https://community.omni.co/t/standardizing-us-state-name-data-to-state-code/288 "2025-03-27T21:55:40Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Becca\_Omni](https://avatars.discourse-cdn.com/v4/letter/b/7c8e57/32.png) [@Becca\_Omni](https://community.omni.co/u/Becca_Omni)\
**Post date:** [March 27, 2025, 9:55pm UTC](https://community.omni.co/t/standardizing-us-state-name-data-to-state-code/288/1 "2025-03-27T21:55:40Z")

</div>

When working with data such as Salesforce data where data validation for US state input fields doesn’t always exist, you may need to ensure the data you’re presenting all rolls up correctly without seeing CA and California represented as distinct rows. Omni makes it easy to do these data transforms directly in the UI which just a few clicks!

To do this in Omni, you can use the below code to either:

- Create a new field
- Transform the field itself

**Code we will be Utilizing**  
You will add your own Table and Field details below for each “TABLE.FIELD” from where you are pulling the state data.

```auto
CASE
    WHEN ${TABLE.FIELD} = 'Alabama' THEN 'AL'
    WHEN ${TABLE.FIELD} = 'Alaska' THEN 'AK'
    WHEN ${TABLE.FIELD} = 'Arizona' THEN 'AZ'
    WHEN ${TABLE.FIELD} = 'Arkansas' THEN 'AR'
    WHEN ${TABLE.FIELD} = 'American Samoa' THEN 'AS'
    WHEN ${TABLE.FIELD} = 'California' THEN 'CA'
    WHEN ${TABLE.FIELD} = 'Colorado' THEN 'CO'
    WHEN ${TABLE.FIELD} = 'Connecticut' THEN 'CT'
    WHEN ${TABLE.FIELD} = 'Delaware' THEN 'DE'
    WHEN ${TABLE.FIELD} = 'District of Columbia' THEN 'DC'
    WHEN ${TABLE.FIELD} = 'Florida' THEN 'FL'
    WHEN ${TABLE.FIELD} = 'Georgia' THEN 'GA'
    WHEN ${TABLE.FIELD} = 'Guam' THEN 'GU'
    WHEN ${TABLE.FIELD} = 'Hawaii' THEN 'HI'
    WHEN ${TABLE.FIELD} = 'Idaho' THEN 'ID'
    WHEN ${TABLE.FIELD} = 'Illinois' THEN 'IL'
    WHEN ${TABLE.FIELD} = 'Indiana' THEN 'IN'
    WHEN ${TABLE.FIELD} = 'Iowa' THEN 'IA'
    WHEN ${TABLE.FIELD} = 'Kansas' THEN 'KS'
    WHEN ${TABLE.FIELD} = 'Kentucky' THEN 'KY'
    WHEN ${TABLE.FIELD} = 'Louisiana' THEN 'LA'
    WHEN ${TABLE.FIELD} = 'Maine' THEN 'ME'
    WHEN ${TABLE.FIELD} = 'Maryland' THEN 'MD'
    WHEN ${TABLE.FIELD} = 'Massachusetts' THEN 'MA'
    WHEN ${TABLE.FIELD} = 'Michigan' THEN 'MI'
    WHEN ${TABLE.FIELD} = 'Minnesota' THEN 'MN'
    WHEN ${TABLE.FIELD} = 'Mississippi' THEN 'MS'
    WHEN ${TABLE.FIELD} = 'Missouri' THEN 'MO'
    WHEN ${TABLE.FIELD} = 'Montana' THEN 'MT'
    WHEN ${TABLE.FIELD} = 'Nebraska' THEN 'NE'
    WHEN ${TABLE.FIELD} = 'Nevada' THEN 'NV'
    WHEN${TABLE.FIELD} = 'New Hampshire' THEN 'NH'
    WHEN ${TABLE.FIELD} = 'New Jersey' THEN 'NJ'
    WHEN ${TABLE.FIELD} = 'New Mexico' THEN 'NM'
    WHEN ${TABLE.FIELD} = 'New York' THEN 'NY'
    WHEN ${TABLE.FIELD} = 'North Carolina' THEN 'NC'
    WHEN ${TABLE.FIELD} = 'North Dakota' THEN 'ND'
    WHEN ${TABLE.FIELD} = 'Northern Mariana Islands' THEN 'MP'
    WHEN ${TABLE.FIELD} = 'Ohio' THEN 'OH'
    WHEN ${TABLE.FIELD} = 'Oklahoma' THEN 'OK'
    WHEN ${TABLE.FIELD} = 'Oregon' THEN 'OR'
    WHEN ${TABLE.FIELD} = 'Pennsylvania' THEN 'PA'
    WHEN ${TABLE.FIELD} = 'Puerto Rico' THEN 'PR'
    WHEN ${TABLE.FIELD} = 'Rhode Island' THEN 'RI'
    WHEN ${TABLE.FIELD} = 'South Carolina' THEN 'SC'
    WHEN ${TABLE.FIELD} = 'South Dakota' THEN 'SD'
    WHEN ${TABLE.FIELD} = 'Tennessee' THEN 'TN'
    WHEN ${TABLE.FIELD} = 'Texas' THEN 'TX'
    WHEN ${TABLE.FIELD} = 'Trust Territories' THEN 'TT'
    WHEN ${TABLE.FIELD} = 'Utah' THEN 'UT'
    WHEN ${TABLE.FIELD} = 'Vermont' THEN 'VT'
    WHEN ${TABLE.FIELD} = 'Virginia' THEN 'VA'
    WHEN ${TABLE.FIELD} = 'Virgin Islands' THEN 'VI'
    WHEN ${TABLE.FIELD} = 'West Virginia' THEN 'WV'
    WHEN ${TABLE.FIELD} = 'Wisconsin' THEN 'WI'
    WHEN ${TABLE.FIELD} = 'Wyoming' THEN 'WY'
    ELSE ${TABLE.FIELD}
END

```

**Creating a New Field**

When you’re in your workbook, on the left hand pane of all views and fields there is an option at the bottom called “+Add Field”. If you click this button, another menu will open to the left and you can add the above SQL in the input field where the arrow is pointing. From there you can give it a name and add to your workbook by clicking “Add”.

 ![Screenshot 2025-03-27 at 2.37.22 PM](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/40957da1d448df37eeec13be48d64a8e1ec48af5.png)

**Updating a Current Field**

When you’re in your workbook, on the left hand pane of all views and fields you can click on the triple dots next to the field you want to modify and then navigate to Modeling \> Edit. From there, you’ll see the “Edit Field” pane come in on the left and you can update the SQL referencing the SQL field noted for the as is field. ie. in the below example, you’d want to reference “Table\_name.BILLING\_STATE”

 ![Screenshot 2025-03-27 at 2.46.34 PM](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/9794ee11890e8a12f4ee4657fed3f5350ccd8427.png)

 ![Screenshot 2025-03-27 at 2.46.56 PM](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/86e6354ac384b3f4e556106c0945dba9801a01b2.png)

Happy Querying!

---

<div class="post-metadata">

**Author:** ![liam](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/liam/32/178_2.png) [@liam](https://community.omni.co/u/liam)\
**Post date:** [March 28, 2025, 12:41pm UTC](https://community.omni.co/t/standardizing-us-state-name-data-to-state-code/288/2 "2025-03-28T12:41:15Z")

</div>

Calcs AI is also great for auto-generating these kind of mappings!  
https://www.loom.com/embed/63428b363312451795ac10564f5f6be4