本文介绍了将管道分隔为新列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！
问题描述
我必须将一列(以竖线分隔)拆分为新列.
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
这篇关于将管道分隔为新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！