将管道分隔为新列

问题描述

我必须将一列(以竖线分隔)拆分为新列.

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

例如:第 1 列:Data|7-8|5

应该拆分成

col2          col3         col4
Data          7-8          5

请帮我解决这个问题.

推荐答案

试试这个.它有点冗长，但说明了操作的每一步.我鼓励您提出您可能遇到的任何后续问题！

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