CODEWITHSUNDEEP
sqlsql-server
Tyler Durden
Tyler Durden

Reputation: 516

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

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.

Upvotes: 1

Views: 533

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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));

Upvotes: 1

Related Questions