create function fn_split_str
(
@s varchar(8000),
@split varchar(10)
) returns @r table (ID VARCHAR(800))
AS
begin
declare @splitlen int
set @splitlen = len(@split+'a') -2
while charindex(@split,@s) >0
begin
insert into @r values (left(@s,charindex(@split,@s)-1))
set @s = stuff(@s,1,charindex(@split,@s)+@splitlen,'')
end
insert into @r values (@s)
return
end
select * from dbo.fn_split_str('1,2,3,45,6',',')
create function fn_split_str1
(
@s varchar(8000),
@pos int,
@split varchar(10)
) returns varchar(100)
AS
begin
declare @splitlen int
set @splitlen = len(@split+'a') -2
while @pos > 1 and charindex(@split,@s+@split) >0
begin
select @pos = @pos -1,@s = stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')
end
return isnull(left (@s,charindex(@split,@s+@split) -1),'')
end
select dbo.fn_split_str1('1,2,3,45,6',4,',')
drop function fn_split_str1
create function fn_split_str2
(
@s1 varchar(4000),
@split varchar(10),
@s2 varchar(4000)
) returns bit
AS
begin
declare @splitlen int
set @splitlen = len(@split+'a') -2
while charindex(@split,@s1) >0
begin
if charindex(@split+left(@s1, charindex(@split,@s1)-1)+@split,@split+@s2+@split) >0
return 1
else
set @s1 = stuff(@s1,1,charindex(@split,@s1)+@splitlen,'')
end
return (case when charindex(@split+@s1+@split,@split+@s2+@split) >0 then 1 else 0 end)
end
select dbo.fn_split_str2('1,2,3,45,6',',','4536')