Microsoft SQL Server自定义函数大全
01、去除字符串中的html标记及标记中的内容
【叶子函数分享一】去除字符串中的html标记及标记中的内容
--1、创建函数
create function [dbo].[clearhtml] (@maco varchar(8000))
returns varchar(8000) as begin
declare @i int
while 1 = 1
begin
set @i=len(@maco)
set @maco=replace(@maco, substring(@maco,charindex('<',@maco),
charindex('>',@maco)-charindex('<',@maco)+1),space(0))
if @i=len( @maco )
break
end
set @maco=replace(@maco,' ','')
set @maco=replace(@maco,' ','')
set @maco=ltrim(rtrim(@maco))
set @maco=replace(@maco,char(9),'')
set @maco=replace(@maco,char(10),'')
set @maco=replace(@maco,char(13),'')
return (@maco)
end
--2、测试示例
declare @mark varchar(8000)
set @mark='<body><div id=u><a href=http://passport.baidu.com/?login&tpl=mn>登录</a></div><center><img src=http://www.baidu.com/img/baidu_logo.gif width=270 height=129 usemap="#mp" id=lg><br><br><br><br><table cellpadding=0 cellspacing=0 id=l><tr><td><div id=m><a onclick=s(this) href=http://news.baidu.com>新 闻</a><b>网 页</b><a onclick=s(this) href=http://tieba.baidu.com>贴 吧</a><a onclick=s(this) href=http://zhidao.baidu.com>知 道</a><a onclick=s(this) href=http://mp3.baidu.com>MP3</a><a onclick=s(this) href=http://p_w_picpath.baidu.com>图 片</a><a onclick=s(this) href=http://video.baidu.com>视 频</a></div></td></tr></table>
<table cellpadding=0 cellspacing=0 style="margin-left:15px"><tr valign=top><td style="height:62px;padding-left:92px" nowrap><div style="position:relative"><form name=f action=/s><input type=text name=wd id=kw size=42 maxlength=100> <input type=submit value=百度一下id=sb><div id=sug onselectstart="return false"></div><span id=hp><a href=/search/jiqiao.html>帮助</a><br><a href=/gaoji/advanced.html>高级</a></span></form></div></td></tr></table>
</body>'
select dbo.clearhtml (@mark)
--3、运行结果
/*
new
---------------------------------------
登录新闻网页贴吧知道MP3图片视频帮助高级
*/
/*
但是上面的函数还存在问题，如果内容中有“《》”或是“<<>>”这样的标记，则不能达到我们的要求。
*/
--加强版
create function [dbo].[clearhtml_V2] (@maco varchar(8000))
returns varchar(8000)
as
begin
declare @randchar_one nvarchar(200)
declare @randchar_two nvarchar(200)
if(charindex('<<',@maco)>0)
begin
set @randchar_one='D4678B36-B958-4274-B81E-BBA636CFB427';
set @randchar_two='49E374CC-9E1A-4850-897C-27074DE32E7F';
set @maco=replace(@maco,'<<',@randchar_one)
set @maco=replace(@maco,'>>',@randchar_two)
end
declare @i int
while 1 = 1
begin
set @i=len(@maco)
set @maco=replace(@maco, substring(@maco,charindex('<',@maco),
charindex('>',@maco)-charindex('<',@maco)+1),space(0))
if @i=len( @maco )
break
end
set @maco=replace(@maco,' ','')
set @maco=replace(@maco,' ','')
set @maco=ltrim(rtrim(@maco))
set @maco=replace(@maco,char(9),'')
set @maco=replace(@maco,char(10),'')
set @maco=replace(@maco,char(13),'')
if(charindex(@randchar_one,@maco)>0)
begin
set @maco=replace(@maco,'D4678B36-B958-4274-B81E-BBA636CFB427','<<')
set @maco=replace(@maco,'49E374CC-9E1A-4850-897C-27074DE32E7F','>>')
end
return (@maco)
end
select dbo.clearhtml_V2('<p>aaaa</p><<本草纲目>><a href="www.baidu.com" />')
--运行结果：
/*
aaaa<<本草纲目>>
*/
特别说明：如果数据量比较大，尽量避免使用自定义函数，以免严重影响性能.
02、去除字符串中连续的分割符
--创建函数 create function [dbo].[m_delrepeatsplit] ( @str varchar(2000), @split nvarchar(200) ) returns nvarchar(2000) as begin --begin declare declare @count int,@i int,@isnull int declare @newchar nvarchar(200),@nn nvarchar(300) set @count=len(@str);set @i=1;set @isnull=1;set @nn=''; --end declare --begin while while @i<@count+1 begin set @newchar=substring(@str,@i,1) if(@isnull=1) begin set @nn=@nn+@newchar; if(@newchar=@split) begin set @isnull=0; end else begin set @isnull=1; end end else begin if(@newchar=@split) begin set @isnull=0; end else begin set @nn=@nn+@newchar; set @isnull=1; end end set @i=@i+1; end --end while return @nn end --2、测试示例 declare @str nvarchar(200) set @str='1 2 3 4 555 6 7 7'; declare @split nvarchar(200) set @split=' '; select dbo.m_delrepeatsplit(@str,@split) as newchar --3、运行结果 /* newchar ------------------ 2 3 4 555 6 7 7 */
03、求第一个字符串中第二个串的个数
--创建函数
create function [dbo].[m_count]
(
@str_one nvarchar(200), --第一个字符串
@str_two nvarchar(200) --第二个字符串
)
returns int as
begin
declare @sqlcount int
select @sqlcount=(len(@str_one)-len(replace(@str_one,@str_two,'')))/len(@str_two)
return @sqlcount
end
--测试示例
select dbo.m_count('sqlserver','e') as [count]
--运行结果
/*
count
-----------
*/
04、综合模糊查询
--创建函数
create function [dbo].[m_fuzzyquery_v1]
(
@str nvarchar(2000)
)
returns nvarchar(2000)
as
begin
declare @count int,@i int;
declare @newchar nvarchar(200),@nn nvarchar(300),@hh nvarchar(200)
set @count=len(@str);set @i=1;set @nn='';
while @i<@count+1
begin
set @newchar=substring(@str,@i,1)+'%'
set @nn=@nn+@newchar;
set @i=@i+1;
end
set @hh='%'+@nn
return @hh
end
--测试数据
declare @table table (connect varchar(30))
insert into @table
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 '程序员一生的活路'
--普通的模糊查询
select * from @table where connect like '%程序生活%'
--运行结果
/*
connect
------------------------------
我的程序生活
*/
--应用函数查询
select * from @table where connect like ( select dbo.[m_fuzzyquery_v1]('程序生活'))
--运行结果
/*
connect
------------------------------
我的程序生活
程序员的生活
程序员一生的活路
*/
05、将十进制转成十六进制
--创建函数
create function [dbo].[hex](@cardno int )
returns varchar (100)
as
begin
declare @temp_mod int
declare @i int
declare @result varchar(100)
declare @temp_x int
declare @result_values int
set @result=''
set @i=1
set @temp_x=0
while @cardno>0
begin
set @temp_mod=@cardno%16
set @cardno=@cardno/16
set @result=(case @temp_mod when 10 then 'A'
when 11 then 'B'
when 12 then 'C'
when 13 then 'D'
when 14 then 'E'
when 15 then 'F'
else ltrim(str(@temp_mod)) end )+@result
end
return @result
end
--测试示例
select [dbo].[hex](1808) as Hex
--运行结果
/*
Hex
----------
*/
--第二版
/****************************
整数转换成进制
作者：不得闲
QQ: 75492895
Email: appleak46@yahoo.com.cn
****************************/
go
Create Function IntToHex(@IntNum int)
returns varchar(16)
as
begin
declare @Mods int,@res varchar(16)
set @res=''
while @IntNum <> 0
begin
set @Mods =@IntNum % 16
if @Mods > 9
set @res = Char(Ascii('A')+@Mods-10)+@res
else
set @res = Cast(@Mods as varchar(4)) + @res
set @IntNum = @IntNum/16
end
return @res
end
--测试示例
select dbo.IntToHex(1808)
--运行结果
/*
*/
06、求两个字符串中相同的汉字及字母的个数
--创建函数
create function [dbo].[funcomparestring]
(
@stra nvarchar(200),
@strb nvarchar(200)
)
returns int as
begin
declare @strbase nvarchar(200)
declare @rank int
select @rank=0
if len(@stra)>len(@strb)
select @rank=count(*)
from funsplitchar(@strb)
where item in(select item from funsplitchar(@stra))
else
select @rank=count(*)
from funsplitchar(@stra)
where item in(select item from funsplitchar(@strb))
return @rank
end
go
--创建第二种函数
create function [dbo].[funcomparestring_new]
(
@stra nvarchar(200),
@strb nvarchar(200)
)
returns int as
begin
declare @strbase nvarchar(200)
declare @rank int
select @rank=0
if len(@stra)>len(@strb)
select @rank=count(*)
from funsplitchar(@strb)
where item in(select distinct item from funsplitchar(@stra))
else
select @rank=count(*)
from
(
select distinct * from funsplitchar(@stra)
where item in(select distinct item from funsplitchar(@strb))
) bb
return @rank
end
--以上两个函数有什么不同呢？下面我用个例子来给大家说明一下：
--测试示例
select [dbo].[funCompareString]('中国Chinese之家','中国人是Chinese')
--结果为：9
select [dbo].[funCompareString_new]('中国Chinese之家','中国人是Chinese')
--结果为：8
--在这两个字符串中，'ese'与'ese'的重复在第一个函数算个字符重复，
--而在第二个函数中算个字符重复。
--也就是说在第二个函数中，多次相同的重复不累积计算例如ese中的e。
07、生成n位随机字符串
--1、借助newid() go --创建视图(因为在函数中无法直接使用newid()) create view vnewid as select newid() N'MacoId'; go --创建函数 create function getrandstr(@n int) returns varchar(max) as begin declare @i int set @i=ceiling(@n/32.00) declare @j int set @j=0 declare @k varchar(max) set @k='' while @j<@i begin select @k=@k+replace(cast(MacoId as varchar(36)),'-','') from vnewid set @j=@j+1 end set @k=substring(@k,1,@n) return @k end --测试示例 select dbo.getrandstr(75) --运行结果 /* D185504AD09C4D5796F7016983E67414CEE25162EA9F43D195D43328A4CF01AC7C586521D8E */ --我们可以发现结果中的字母都是大写的，或是都是小写的。 --换种方法来写下： go --创建函数 create function [dbo].[m_rand](@mycount int) returns nvarchar(2000) as begin declare @maco_wang table (id varchar(1)) declare @maco_number int,@number int; declare @my_one nvarchar(max),@my_two nvarchar(max) set @my_one='';set @maco_number=0; set @number =48; while (@number>=48 and @number<=57) or (@number>=65 and @number<=90) or (@number>=97 and @number<=122) begin insert into @maco_wang select char(@number) set @number=@number+1; if(@number=58) begin set @number=65 end if(@number=91) begin set @number=97 end end while @maco_number<@mycount begin select @my_two=id from @maco_wang order by (select MacoId from dbo.m_macoview); set @my_one=@my_two+@my_one; set @maco_number=@maco_number+1; end return @my_one end --测试用例 select [dbo].[m_rand](75) --运行结果 /* 5nN0w4o4VOkjacB5so2uvCuw2ZRrnBhxEi4IcsEOHzBbStKmR1p8ASH4N4XaxhDoDEtkX8bZ0CR */
08、取出字符串中的汉字、字母或是数字
go
--创建函数(得到字符串中的汉字)
create function [dbo].[m_getchinese]
(
@chinese nvarchar(max)
)
returns varchar(100)
as
begin
while patindex('%[^吖-咗]%',@chinese) > 0
begin
set @chinese = stuff(@chinese,patindex('%[^吖-咗]%',@chinese),1,N'');
end
return @chinese
end
go
--创建函数(得到字符串中的字母)
create function [dbo].[m_getstr](@maco varchar(100))
returns varchar(max)
as
begin
while patindex('%[^a-z]%',@maco) > 0
begin
set @maco=stuff(@maco,patindex('%[^a-z]%',@maco),1,'')
end
return @maco
end
go
--创建函数(得到字符串中的数字)
create function [dbo].[m_getnumber]
(
@mysql_one nvarchar(200)
)
returns varchar(200)
begin
declare @mysql_two varchar(200)
select @mysql_two=
substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),patindex('%[^0-9.]%',substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),
len(@mysql_one)-patindex('%[0-9.]%',@mysql_one)+1))-1)
return @mysql_two;
end
--测试
select dbo.[m_getchinese]('China2009中国HRB4-1v')
select dbo.[m_getstr]('China2009中国HRB4-1v')
select dbo.[m_getnumber]('China2009中国HRB4-1v')
--运行结果
/*
-----------
中国
-----------
ChinaHRBv
-----------
*/
--说明一下
--上面这个取数字是可以取浮点型的
select dbo.[m_getnumber] ('字段.456A(AA)A')--正常
select dbo.[m_getnumber] ('CHinese2.1day')--正常
select dbo.[m_getnumber] ('Name5.01From')--正常
select dbo.[m_getnumber] ('9898Address')--正常
select dbo.[m_getnumber] ('aaaaaForm2.3333')--错误
--修正函数
go
/* 取出字符串中间的数字(第二版)*/
create function [dbo].[m_getnumberV2.0]
(
@mysql_one nvarchar(200)
)
returns varchar(200)
begin
declare @mysql_two varchar(200)
declare @sql_one int
declare @sql_two int
select @sql_one= patindex('%[0-9.]%',@mysql_one)
select @sql_two=
patindex('%[^0-9.]%',
substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),len(@mysql_one)-patindex('%[0-9.]%',@mysql_one)+1))
if @sql_two=0
begin
select @mysql_two= substring (@mysql_one,@sql_one,len(@mysql_one)+1-@sql_one)
end
else
begin
select @mysql_two=substring (@mysql_one,@sql_one,@sql_two-1)
end
return @mysql_two;
end
--测试示例
select dbo.[m_getnumberV2.0] ('字段.456A(AA)A')--正常
select dbo.[m_getnumberV2.0] ('CHinese2.1day')--正常
select dbo.[m_getnumberV2.0] ('Name5.01From')--正常
select dbo.[m_getnumberV2.0] ('9898Address')--正常
select dbo.[m_getnumberV2.0] ('aaaaaForm2.3333')--正常
09、根据字符分割字符串的三种写法
go --创建函数(第一版) create function [dbo].[m_split](@c varchar(2000),@split varchar(2)) returns @t table(col varchar(200)) as begin while(charindex(@split,@c)<>0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end insert @t(col) values (@c) return end --下面两种是在论坛看到高手们发的 go --创建函数(第二版)(fredrickhu(小F)) create function [dbo].[f_split](@s varchar(8000), @split varchar(10) ) returns table as return (select substring(@s,number,charindex(@split,@s+@split,number)-number)as col from master..spt_values where type='p' and number<=len(@s+'a') and charindex(@split,@split+@s,number)=number) go --创建函数(第三版)(dawugui(爱新觉罗.毓华)) create function [dbo].[d_split] (@inputstr varchar(8000),@seprator varchar(10)) returns @temp table (a varchar(200)) as begin declare @i int set @inputstr
最低0.47元/天 解锁文章
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
