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',',')
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.