字符串截取

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

转载于:https://www.cnblogs.com/kasafuma/archive/2008/03/18/1111115.html