字符串成单列 [英] string into single column

问题描述

我将逗号分隔的字符串如下所示转换为一列.

I have the comma separated string as shown below to convert into a column.

示例:

给定字符串:

DECLARE @STR VARCHAR(MAX) = 'ABC,DEF,GHI,JKL,MNO'

必须转换为单列:

columnName
-----------
ABC
DEF
GHI
JKL
MNO

推荐答案

试试这个.使用 LTRIM 和 RTRIM 函数去除前导和尾随空格.

Try this. Used LTRIM and RTRIM function to remove Leading and Trailing spaces.

DECLARE @STR VARCHAR(MAX) = 'ABC,DEF,GHI,JKL,MNO'

SELECT Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))) Split_Data
FROM   (SELECT Cast ('<M>' + Replace(@STR, ',', '</M><M>') + '</M>' AS XML) AS Data) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a) 

输出:

Split_Data
----------
ABC
DEF
GHI
JKL
MNO

这篇关于字符串成单列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！