Creating a computed Int column in SQL Server from a text column-CodePudding

I want to create a new column in an existing SQL Server table that utilizes state abbreviations (an existing column) to recode the states into a number (1-50) for statistical analysis that will be performed after exporting the output.

Assuming that Alabama is AL = 1 and Wyoming is WY = 50, how would I go about doing this for every state?

CodePudding user response：

Two ways:

Create a lookup table, STATE_ID (with values 1..50) and STATE_ABBREV ('AL' to 'WY'), then join on this table.
Create a large CASE statement:

CASE STATE_ABBR
  WHEN 'AL' THEN 1
  ...
  WHEN 'WY' THEN 50
  ELSE NULL
END AS STATE_ID

Using a lookup table is really preferred, as this puts the logic into a single place in case it gets used elsewhere. Plus, this is really data, and coding it into a view as code is not the right way to go.

CodePudding user response：

A different approach, if you don't want to use a temporary or lookup table. Create a list of all states in the alphabetical order, and use the charindex function as shown below

create table #temp
( stateCode char(2))
INSERT INTO #temp values ('PA'),('AL'),('NJ'),('MA'),('DC')

select StateCode,charindex(Statecode '|','AL|PA|NJ|NY|MA|DC|')/3 1 as numb
from #temp
order by numb

Note that charindex is slow, but this approach could be adapted to your computed field if you want.

Again, I think a lookup table is a better solution, but if it has to be a computed field, this should work for you