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   @L_301_0@
  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 smallintasbegindeclare @a smallint,@b smallintset @a=0set @b=1while @b<=len(@thestr)    begin      if subString(@thestr,@b,len(@searchstr))=@searchstr begin set @a=@a+1 end      set @b=@b+1    endreturn @aend