Sql server

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.