I want to create new columns in a table that currently has two columns: "dx" and "dxlength". The new columns will be a substring of characters from column "dx" based on the length of characters in "dx" which can be found in column "dxlength".
For example, if the dxlength is >=3, then I would like to put the first three characters into a column named "first3".
I am trying to use a case when statement, but am getting several errors. What do I need to change to my code?
I have tried using a case when statement. See example code below.
select dx, dxlength,
case when dxlength>=3 then substring('dx',1,3) as first3,
case when dxlength>=4 then substring('dx',1,4) as first4,
case when dxlength>=5 then substring('dx',1,5) as first5,
case when dxlength>=5 then substring('dx',5,1) as fifth,
case when dxlength>=6 then substring('dx',6,1) as sixth,
case when dxlength<7 then '!' as seventh,
case when dxlength=7 then substring('dx',7,1) as seventh
into table2
from table1
The table would have 7 additional columns that contain a substring of the characters in column "dx" based on the number of characters in "dx", which can be found in column "dx length".
Aucun commentaire:
Enregistrer un commentaire