函数收集1

[azsoft]

这里收集的有关函数，仅“抛砖引玉”。
具体的函数应该在运用时灵活使用。
注：创建函数不支持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

[azsoft]

8、马可的：

create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
-执行
select * from dbo.f_splitstr('asdfcv,qe.ehxa.what about this key i;qe, adsf.dfawgf','.')
where f1 like '%key%'

9、列转行函数
------------------------------------------
create function test(@id int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+Description+',' from 表 where ID=@id
set @sql=left(@sql,len(@sql)-1)
return(@sql)
end

--执行
select distinct ID,dbo.test(ID) as Description from 表

10、
---------------------------------------------------------
txlicenhe（原作）


P031106001 -- 第一位P表示采购单,031106表示日期，后三位是流水号。　

/* 1:创建测试用的表结构 */
create table tablename(pono char(10),b int)
go
/* 2:创建一个得到当前日期的视图，为下面的自字义函数作准备 */
create view vGetdate
as
select getdate() as today
go
/* 3:用自定义函数来得到单号(因自定函数内不能用getdate()来得到当前日期，要用到上面的视图) */
create function getDH()
returns char(10)
As
begin
declare @dh1 char(10),@dh2 char(10)
select @dh1 = max(pono) from tableName
Set @dh1 = IsNull(@dh1,'P000000000')
select @dh2 = Left(@dh1,1) + right(convert(varchar(8),today,112),6) + '001' from vGetdate
if @dh1 >= @dh2
begin
set @dh2 = left(@dh1,7) + right('000'+ cast(cast(right(@dh1,3) as int)+1 as varchar),3)
end
return(@dh2)
end
go
/* 4:在字段默认值中填入 dbo.getdh() */
alter table tablename add constraint df_tablename_1 default(dbo.getdh()) for pono
-/* 5:测试：*/
insert tablename(b) values(1)
insert tablename(b) values(2)
Select * from tablename
-- 测试结果
pono b
---------- -----------
P031115001 1
P031115002 2

[gmlxf]

/*
函数名：f_CompTwoStr
功能：判断闰年
参数：
@Year---字符串
返回：
1---是
0---否
*/
/*判断闰年*/
drop function f_IsLeapYear
go
create function f_IsLeapYear
(
@Year varchar(4)
)
returns bit
as
begin
-- 方法一：
if (@Year%4=0 and @Year%100<>0) or @Year%400=0
-- 方法二：if isdate(@Year + '-2-29')=1
-- 方法三：if day(cast((@year + '-3-1') as datetime)-1)=29
return 1
return 0
end
go
-- 调用
select dbo.f_IsLeapYear('3000')
select dbo.f_IsLeapYear('2004')

[gmlxf]

/*
函数名：f_CompTwoStr
功能：比较两个字符串是否有连续相同个数的字符
参数：
@str1---字符串1
@str1---字符串2
@n---连续相同的个数
返回：
1---存在
0---不存在
Author:gmlxf(烛光)2003-12-16
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_CompTwoStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_CompTwoStr]
go

create function f_CompTwoStr
(
@str1 varchar(8000),
@str2 varchar(8000),
@n int
) returns bit
as
begin
declare @i int
set @i=1
while @i<=len(@str1)-@n+1
begin
if charindex(substring(@str1,@i,@n),@str2)>0
return 1
set @i=@i+1
end
return 0
end
go

--test:
select dbo.f_CompTwoStr('浦东：田园小区148号405室','浦：田园小区148幢405室',5)
select dbo.f_CompTwoStr('浦东：田园小区148号405室','浦：田园小区148幢405室',8)
select dbo.f_CompTwoStr('浦东：田园小区148号405室','浦：田园小区148幢405室',9)

[gmlxf]

/***********得到最小缺号函数***********
唯一编号要求：需要是int类型
--------by /gmlxf/csdn/2003.11.20/-----******/

if exists(select 1 from dbo.sysobjects where id=object_id(N'dbo.f_getMinShortNum') and objectproperty(id,N'IsInlineFunction')=0)
drop function f_getMinShortNum
go
create function f_getMinShortNum() returns int
as
begin
declare @MinShortNum int
select @MinShortNum=min(id)+1 from (select id from test union select 0) a where not exists (select 1 from test where id=a.id+1)
--也可：select @MinShortNum=min(id)+1 from (select id from test union select 0) a where id not in (select id-1 from test)
return @MinShortNum
end
go

--示例(创建一个表test,里面的id号码存在缺号：1,3,4)
create table test(id int)
insert test
select 2
union select 4
union select 6
union select 7
go
select id from test

--调用
select dbo.f_getMinShortNum()
--插入数据
insert test values(dbo.f_getMinShortNum()) --插入了id=1的一行
insert test values(dbo.f_getMinShortNum()) --插入了id=3的一行
select id from test order by id
drop table test


---转个行转列的
CREATE TABLE #t
(
Project INT,
ClassA INT ,
ClassB INT,
ClassC INT
)
INSERT INTO #t VALUES (123,1,2,3)
INSERT INTO #t VALUES (234,4,5,6)

SELECT * FROM #t

SELECT Class, MAX("123") '123', MAX("234") '234'
FROM ( SELECT CASE c WHEN 'x' THEN 'ClassA'
WHEN 'y' THEN 'ClassB'
WHEN 'z' THEN 'ClassC'
END,
CASE WHEN c = 'x' AND Project = 123 THEN ClassA
WHEN c = 'y' AND Project = 123 THEN ClassB
WHEN c = 'z' AND Project = 123 THEN ClassC
END,
CASE WHEN c = 'x' AND Project = 234 THEN ClassA
WHEN c = 'y' AND Project = 234 THEN ClassB
WHEN c = 'z' AND Project = 234 THEN ClassC
END
FROM #T
CROSS JOIN ( SELECT 'x' UNION
SELECT 'y' UNION
SELECT 'z' ) D ( c )
) D (Class, "123", "234")
GROUP BY Class

SELECT 'ClassA' Class,MAX(CASE WHEN Project=123 THEN ClassA END) '123',
MAX(CASE WHEN Project=234 THEN ClassA END) '234'
FROM #t
UNION
SELECT 'ClassB',MAX(CASE WHEN Project=123 THEN ClassB END) '123',
MAX(CASE WHEN Project=234 THEN ClassB END) '234'
FROM #t
UNION
SELECT 'ClassC',MAX(CASE WHEN Project=123 THEN ClassC END) '123',
MAX(CASE WHEN Project=234 THEN ClassC END) '234'
FROM #t

DROP TABLE #T

[txlicenhe]

收集了那么多啊。再来。