SQL script to fill in two columns with the data of another one

1
votes

I need your help to tidy a table. I added two new columns. I would like to fill in these columns(col2, col3) with the data of another one (col1). At the moment, this column contain two values separate by a "val1;val2".

col1 contains a string but sometimes val1 doesn't exist and col1 contains only ";val2"

For every row of this table, I want to split the value in the col1 to separate the date execute this code:

  1. If(val1 != null) col3 = 1
  2. col2 = val2
  3. col1 = val1

Finally, every column contains only one value.

Before the script:
col1 (string) = "tom;car"  

After the script:   
col1 (string) = "tom"   
col2 (string) = "car" 
col3 (bit) = "1"

I don't know how to do this directly in SQL Server Management. I would like to create a script to execute this code.

1
sqlsql-server
You should give example data. For example: col1 contains 42,4711. And you want to finally have 42 in col2 and 4711 in col3. Will they be numeric? will they be strings? will they be timestamps or dates? All that can affect the necessary code. - marcothesane
Sample data and desired results would really help explain what you want to do. - Gordon Linoff

1 Answers

1
votes

Something like this?

update t
    set col3 = (case when col1 not like ';%' then 1 else col3 end),
        col1 = left(col1, charindex(';', col1) - 1),
        col2 = substring(col1, charindex(';', col1) + 1, len(col1));