sqlserver分隔字串，尋找父類下所有子類，重複資料刪除字串，計算一字串在別一字元中出現的次數

/*dnt下面分隔字串函數*/

create FUNCTION [dnt_split]
(
 @splitstring NVARCHAR(4000),
 @separator CHAR(1) = ','
)
RETURNS @splitstringstable TABLE
(
 [item] NVARCHAR(200)
)
AS
BEGIN
    DECLARE @currentindex INT
    DECLARE @nextindex INT
    DECLARE @returntext NVARCHAR(200)
    SELECT @currentindex=1
    WHILE(@currentindex<=datalength(@splitstring)/2)
    BEGIN
        SELECT @nextindex=charindex(@separator,@splitstring,@currentindex)
        IF(@nextindex=0 OR @nextindex IS NULL)
            SELECT @nextindex=datalength(@splitstring)/2+1
       
        SELECT @returntext=substring(@splitstring,@currentindex,@nextindex-@currentindex)
        INSERT INTO @splitstringstable([item])
        VALUES(@returntext)
       
        SELECT @currentindex=@nextindex+1
    END
    RETURN
END

 

/*尋找分類下面所有子類*/

create     function   f_child(@typeId)  
  returns   @re   table(typeId  int,Level   int)  
  as  
  begin  
  declare   @l   int  
  set   @l=0  
  insert   @re   select   @typeId,@l  
  while   @@rowcount>0 

  begin  
  set   @l=@l+1  
  insert   @re   select   a.typeid,@l  
  from   product_type   a   join   @re   b   on   a.fatherId=b.typeId  
  where   b.level=@l-1
  end  
  return  
  end 

 

/*刪除以逗號分隔的重複字串*/

create function DeleteRepeatStrs
(@strs varchar(100))
returns varchar(100)
as
begin
declare @noRepeatStr varchar(100)
declare @index int
set @noRepeatStr=''
declare @id varchar(10)
declare repeatCursor cursor for select * from dnt_split(@strs,',')
open repeatCursor
fetch next from repeatCursor into @id
 while @@fetch_status=0
 begin
  select @index=charindex(@id,@noRepeatStr,0)
  if @index=0
  begin
   set @noRepeatStr=@noRepeatStr+@id+','
  end
  fetch next from repeatCursor into @id
 end
set @noRepeatStr=substring(@noRepeatStr,1,len(@noRepeatStr)-1)
close repeatCursor
deallocate repeatCursor
return @noRepeatStr
end

 

/*計算參數2在參數1中出現的次數*/

ALTER   function cisum(@thestr varchar(1000),@searchstr varchar(100))
returns smallint
as
begin
declare @a smallint,@b smallint
set @a=0
set @b=1
while @b<=len(@thestr)
    begin
      if substring(@thestr,@b,len(@searchstr))=@searchstr begin set @a=@a+1 end
      set @b=@b+1
    end
return @a
end