Ritesh Ritesh - 6 months ago 17
SQL Question

Split pipe delimited into new columns

I have to split one column (pipe delimited) into new columns.

Ex: column 1:

Data|7-8|5


it should be split into

col2 col3 col4
Data 7-8 5


Please help me to solve this.

Answer

Have a play with this. It's a little verbose but illustrates every step of the operation. I encourage you to ask any follow up questions you might have!

DECLARE @t table (
   piped varchar(50)
)

INSERT INTO @t (piped)
  VALUES ('pipe|delimited|values')
       , ('a|b|c');

; WITH x AS (
  SELECT piped
       , CharIndex('|', piped) As first_pipe
  FROM   @t
)
, y AS (
  SELECT piped
       , first_pipe
       , CharIndex('|', piped, first_pipe + 1) As second_pipe
       , SubString(piped, 0, first_pipe) As first_element
  FROM   x
)
, z AS (
  SELECT piped
       , first_pipe
       , second_pipe
       , first_element
       , SubString(piped, first_pipe  + 1, second_pipe - first_pipe - 1) As second_element
       , SubString(piped, second_pipe + 1, Len(piped) - second_pipe) As third_element
  FROM   y
)
SELECT *
FROM   z