CODEWITHSUNDEEP
sql-server
John Nyingi
John Nyingi

Reputation: 1110

Split String in to different SQL Server columns

I have a string in a table that I need to split into different columns, this is my string;

"VEX_NAME=LILIAN BRIGHT||VEX_ID=3006030"

I would like to be split in the format below

  VEX_ID  |  VEX_NAME
  --------+----------------
  3006030 | LILIAN BRIGHT

Upvotes: 0

Views: 74

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use substring() & charindex() :

select substring(col, charindex('VEX_ID=', col) + 7, len(col)) as VEX_ID,
       substring(col, charindex('VEX_NAME=', col) + 9, 
                 charindex('||', col) - (charindex('VEX_NAME=', col) + 9 )) as VEX_NAME
from table t;

However, you can also use APPLY :

select substring(col, VEX_ID, len(col)) as VEX_ID,
       substring(col, VEX_NAME, pipe - VEX_NAME) as VEX_NAME
from table t cross apply 
    ( values (charindex('VEX_ID=', col) + 7, charindex('VEX_NAME=', col) + 9,  charindex('||', col))
    ) tt(VEX_ID, VEX_NAME, pipe);

Upvotes: 4

Related Questions