Split String in to different SQL Server columns

Question

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

Which version of SQL Server are you using - it will be relevant to this answer — StuartLC
– StuartLC, Commented Apr 10, 2019 at 10:39

Yogesh Sharma · Accepted Answer · 2019-04-10 11:03:21Z

4

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

edited Apr 10, 2019 at 11:03

answered Apr 10, 2019 at 10:39

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Split String in to different SQL Server columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related