问题描述
我必须将一列(以竖线分隔)拆分为新列.
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