In this programming tutorial we will learn how to create and use split function in ms sql server to split comma separated string into table. Ms sql server does not have any built-in split function. In this tutorial we will create a split function to convert a comma-separated string value such as (‘adeel fakhar,abdur rehman,Irfan Ghani’) into a temporary table with each string as rows.

Split Function in ms sql server to split comma separated string into table

The below mentioned split function is table-valued function that will help us to split comma separated (or any other delimiter value) string to individual strings.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (username varchar(8000))     
as     
begin     
	declare @idx int     
	declare @slice varchar(8000)     
    
	select @idx = 1     
		if len(@String)<1 or @String is null  return     
    
	while @idx!= 0     
	begin     
		set @idx = charindex(@Delimiter,@String)     
		if @idx!=0     
			set @slice = left(@String,@idx - 1)     
		else     
			set @slice = @String     
		
		if(len(@slice)>0)
			insert into @temptable(username) values(@slice)     

		set @String = right(@String,len(@String) - @idx)     
		if len(@String) = 0 break     
	end 
return     
end

The Split function takes two parameters, first one is the string and second one is the delimiter to split that string. To call this function, you will write the following statement

select * from dbo.split('adeel fakhar,abdur rehman,irfan ghani',',')

Output:-
adeel fakhar
abdur rehman
irfan ghani

So that's it. This is the way to create and use split function in sql server.

I hope you will find this tutorial very informative.

I love your feedback.