Thursday, 12 November 2015

Converting Comma Separated Column value into multiple rows by T-SQL

DECLARE @SearchTable table(ID int,Ttext varchar(4000));
insert into @SearchTable
Select 1,N'Headset Ball Bearings Chainring Nut This is illegal Sentence'
union
Select 2,N'This is Correct Sentence'


select A.ID,
     Split.a.value('.', 'VARCHAR(100)') AS Words
 FROM  (SELECT ID,
         CAST ('<M>' + REPLACE(Ttext, ' ', '</M><M>') + '</M>' AS XML) AS Words
FROM  @SearchTable) AS A CROSS APPLY Words.nodes ('M') AS Split(a)

Cheerss,

No comments:

Post a comment