|
/*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 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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
