string into single column

Question

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

Example:

Given String:

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

Have to convert into a single column:

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

Answer 1

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) 

OUTPUT :

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