函数收集1
这里收集的有关函数,仅“抛砖引玉”。
具体的函数应该在运用时灵活使用。
注:创建函数不支持SQL7.0
1、取字符串里的数字
create function interceptStr(@str varchar(8000))
returns varchar(8000)
as
begin
declare @i int,@j int,@num varchar(8000),@Result varchar(8000)
set @i=1
set @Result=''
set @j=0
while @i<len(@str)+@j
begin
set @num=substring(@str,@i,1)
if @num like '[0-9]'
begin
set @Result=@Result+@num
set @j=@j+1
end
set @i=@i+1
end
if @Result=''
set @Result='0'
return(@Result)
end
用法:
select dbo.interceptstr('sasad1323fdsfdsf4567')
2、一个合并的函数,可以自定义分隔符
create function f_merg(@a char(1),@b char(3),@spch varchar(10))
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+@spch+rtrim(colC) from T_A where colA= @a and ColB=@b
set @re=substring(@re,len(@spch)+1,8000)
return(@re)
End
go
3、取拼音函数
create function fGetPy(@Str varchar(500)='')
returns varchar(500)
as
begin
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)
select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if @chn>'z'
select @n = @n +1
,@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn = '吖'
union all select '八'
union all select '嚓'
union all select '咑'
union all select '妸'
union all select '发'
union all select '旮'
union all select '铪'
union all select '丌' --because have no 'i'
union all select '丌'
union all select '咔'
union all select '垃'
union all select '嘸'
union all select '拏'
union all select '噢'
union all select '妑'
union all select '七'
union all select '呥'
union all select '仨'
union all select '他'
union all select '屲' --no 'u'
union all select '屲' --no 'v'
union all select '屲'
union all select '夕'
union all select '丫'
union all select '帀'
union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
else set @c='a'
set @return=@return+@c
end
return(@return)
end
go
----------------以下收集:zjcxc(邹建)-----------------
4、/*--数据完整性检查--*/
--自定义函数--检测某个编码出发,是否被循环引用
create function f_chkid(@id int)
returns bit --循环,返回1,否则返回0
as
begin
declare @re bit,@pid int
set @re=0
--检测
select @pid=pid from tb where id=@id
while @@rowcount>0
begin
if @pid=@id
begin
set @re=1
goto lbErr
end
select @pid=pid from tb where id=@pid
end
lbErr:
return(@re)
end
go
--显示表中的那些数据不符合规范
select * from tb a
where not exists(select 1 from tb where id=a.pid)
or dbo.f_chkid(id)=1
go
/*--数据复制
如果表中包含自定义字段,需要修改存储过程
存在嵌套不超过32层的问题.
--*/
--创建复制的存储过程--复制指定结点下的子结点到另一个结点下
create proc p_copy
@s_id int, --复制该项下的所有子项
@d_id int, --复制到此项下
@new_id int --新增加项的开始编号
as
declare @nid int,@oid int,@name varchar(20)
select id,name into #temp from tb where pid=@s_id and id<@new_id
while exists(select 1 from #temp)
begin
select @oid=id,@name=name from #temp
insert into tb values(@d_id,@name)
set @nid=@@identity
exec p_copy @oid,@nid,@new_id
delete from #temp where id=@oid
end
go
--创建批量复制的存储过程--复制指定结点及其下面的所有子结点,并生成新结点
create proc p_copystr
@s_id varchar(8000) --要复制项的列表,用逗号分隔
as
declare @nid int,@oid int,@name varchar(20)
set @s_id=','+@s_id+','
select id,name into #temp from tb
where charindex(','+cast(id as varchar)+',', @s_id)>0
while exists(select 1 from #temp)
begin
select @oid=id,@name=name from #temp
insert into tb values(@oid,@name)
set @nid=@@identity
exec p_copy @oid,@nid,@nid
delete from #temp where id=@oid
end
go
--测试
exec p_copystr '5,6'
--显示处理结果
select * from tb order by dbo.f_getmergid(id)
go
--删除数据测试环境
drop table tb
drop function f_getmergid,f_chkid
drop proc p_delete,p_copystr,p_copy
5、/*-- 得到级别 --*/
create function f_getidlevel(@id int)
returns int
as
begin
declare @re int,@pid int
set @re=1
--得到级别(深度)
select @pid=pid from tb where id=@id
while @@rowcount>0
select @re=@re+1
,@pid=pid from tb where id=@pid
return(@re)
end
go
--调用
select *,dbo.f_getidlevel(id) from tb
6、/*-- 得到指定id的子id列表 --*/
--不包含排序字段的情况
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
insert into @re select id from tb where pid=@id
while @@rowcount>0
insert into @re select a.id
from tb a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go
7、 --包含排序字段的情况
create function f_getchildidsort(@id int)
returns @re table(id int,sortid varchar(8000))
as
begin
--为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20)
select @idlen=max(len(id))
,@idheader=space(@idlen)
from tb
insert into @re select id,right(@idheader+cast(id as varchar),@idlen)
from tb where pid=@id
while @@rowcount>0
insert into @re select a.id,right(@idheader+cast(a.id as varchar),@idlen)+','+b.sortid
from tb a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go
--调用示例,显示1的所有子.
select a.* from tb a inner join dbo.f_getchildidsort(1) b on a.id=b.id order by b.sortid