string into single column [duplicate]

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

Many dups about splitting a string; Tsql split string

Alex K.
– Alex K.

2014-12-02 17:35:59 +00:00
Commented Dec 2, 2014 at 17:35 — Alex K.
– Alex K., Commented Dec 2, 2014 at 17:35

Pரதீப் · Accepted Answer · 2014-12-02 17:49:39Z

2

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

edited Dec 2, 2014 at 17:49

answered Dec 2, 2014 at 17:40

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MAK Over a year ago

Great! Thank you so much. Will accept this within 5 min.

Collectives™ on Stack Overflow

string into single column [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related