Split string table value function sql server

Split string table value function sql server

Function return a table.

@String : value with separator ex: 1,2,3
@Delimiter : define separator as character

if pass blank string then return one row with null you can set default value.

Function with sql server script:
Copy the below script execute in database.

example:

select * from [dbo].split('1,2,3',',')

Result:



Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1)) 
returns @temptable TABLE (items varchar(8000)) 
as 
begin 
 declare @idx int 
 declare @slice varchar(8000) 
 
 select @idx = 1 
 if len(@String)<1 or @String is null 
 begin
 insert into @temptable (Items) values(NULL)
 return
 end
 
 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(Items) values(@slice) 
 
 set @String = right(@String,len(@String) - @idx) 
 if len(@String) = 0 break 
 end 
return 
end