How To Split A String In SQL

Introduction

Hello Everyone,

I am going to take advantage of this time to write code for a split comma separated string without using functions. In general, we face this type of scenario in real working conditions, and we can expect this in interview questions. Now, I came up with different resolutions for this. We can use Recursive CTE to split a comma-separated string in SQL. Instead of a string, we can use columns in our tables also. In my current project, I got raw data in a comma-separated string format, and I used it to split the string into rows and insert them into my tables.

declare @a varchar(100)
set @a = 'vinay,talapaneni,Hello,HI'
;with cte as(select STUFF(@a,1,CHARINDEX(',',@a),'') as number,
convert(varchar(50),left(@a, CHARINDEX(',',@a)-1 )) col1
union all
select STUFF(number,1,CHARINDEX(',',number+','),'') number,
convert(varchar(50),left(number,(case when CHARINDEX(',',number) = 0 then len(number) else CHARINDEX(',',number)-1 end)) )col1
from cte where LEN(number) >0
)
select col1 from cte

My string contains 4 words and is separated by a comma. I want to split 4 words into 4 rows. In the above code, I used Recursive CTE to achieve my goal. The first part was to hide the first word in a string in the Number column and the hidden word will appear in the Col1 column. The second part of the Cte was to use the output of the number column in the first part and split that string accordingly. After executing the above code we get results as shown below.

I have another approach to achieve the same result. If you are familiar with XML, the below command can be placed directly in your join and where conditions as well.

declare @a varchar(100)
set @a = 'vinay,talapaneni,Hello,HI'
select
a.value('.', 'varchar(max)')
from
(select cast('<M>' + REPLACE(@a, ',', '</M><M>') + '</M>' AS XML) as col) as A
CROSS APPLY A.col.nodes ('/M') AS Split(a)

After executing this code, you get the same result shown above.