Simple split function in SQL

By | August 17, 2012

Most of the times while designing procedures and cursors for customization you may need to use a function which will split your string based on predefined delimiter and return you items set. Below is the function you can create and use in SQL for the same.
CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
returns @Results TABLE (Items nvarchar(4000))
declare @index int
declare @slice nvarchar(4000)
select @index = 1
if @String is null return
while @index != 0
select @index = charindex(@Delimiter,@String)
if @index !=0
select @slice = left(@String,@index – 1)
select @slice = @String
insert into @Results(Items) values(@slice)
select @String = right(@String,len(@String) – @index)
if len(@String) = 0 break
end return
E.g. :
select * from dbo.split(‘Greytrix India Pvt Ltd.’, ”)