Microsoft SQL Server 自定义函数整理大全
转：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://image.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 ------------------ 1 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 ----------- 2 */
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 ---------- 710 */ --第二版 /**************************** 整数转换成进制 作者：不得闲 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) --运行结果 /* 710 */
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 ----------- 2009 */ --说明一下 --上面这个取数字是可以取浮点型的 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 = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator, @inputstr) while @i >= 1 begin insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) set @i = charindex(@seprator, @inputstr) end if @inputstr <> '/' insert @temp values(@inputstr) return end --测试示例 declare @sql varchar(20) set @sql='A,B,C,D,E' select * from dbo.m_split(@sql,',') select * from dbo.f_split(@sql,',') select * from dbo.d_split(@sql,',') --运行结果(结果是相同的) /* col --------- A B C D E */
10、将数字转换千分位分隔形式
--创建函数(原创：dobear_0922) create function [dbo].[getformatstring](@dec decimal(28,8), @n int) returns varchar(32) as begin declare @str varchar(32), @len int, @left varchar(32), @right varchar(32),@end varchar(32) if @n!='0' begin set @str= round(@dec,@n) select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2 while @len>1 begin select @left=stuff(@left,@len,0,','), @len=@len-3 end select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4 while @len <=len(@right) begin select @right=stuff(@right,@len,0,','), @len=@len+4 end set @end= @left+'.'+@right end else begin set @str= round(@dec,@n) select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2 while @len>1 begin select @left=stuff(@left,@len,0,','), @len=@len-3 end select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4 while @len <=len(@right) begin select @right=stuff(@right,@len,0,','), @len=@len+4 end set @end= @left end return @end end --测试示例 select [dbo].[getformatstring](2645433,2) --运行结果 /* 2,645,433.00 */ --如果小数点后面不需要处理的话，我们可以不用函数直接： select convert(varchar, convert(money, 2645433), 1) /* 2,645,433.00 */
11、取汉字首字母的两个函数
go --创建取汉字首字母函数(第一版) create function [dbo].[f_getpy_V1] (@str nvarchar(4000)) returns nvarchar(4000) as begin declare @word nchar(1),@py nvarchar(4000) set @py='' while len(@str)>0 begin set @word=left(@str,1) set @py = @py+ (case when unicode(@word) between 19968 and 19968+20901 then ( select top 1 py from ( select 'a' as py, N'驁' as word union all select 'B',N'簿' union all select 'C',N'錯' union all select 'D',N'鵽' union all select 'E',N'樲' union all select 'F',N'鰒' union all select 'G',N'腂' union all select 'H',N'夻' union all select 'J',N'攈' union all select 'K',N'穒' union all select 'L',N'鱳' union all select 'M',N'旀' union all select 'N',N'桛' union all select 'O',N'漚' union all select 'P',N'曝' union all select 'Q',N'囕' union all select 'R',N'鶸' union all select 'S',N'蜶' union all select 'T',N'籜' union all select 'W',N'鶩' union all select 'X',N'鑂' union all select 'Y',N'韻' union all select 'Z',N'咗' ) T where word>=@word collate Chinese_PRC_CS_AS_KS_WS order by py asc ) else @word end) set @str=right(@str,len(@str)-1) end return @PY end go --创建取汉字首字母函数(第二版) create function [dbo].[f_getpy_V2](@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 --测试示例 select dbo.[f_getpy_V1]('王立国') as V11 select dbo.[f_getpy_V2]('王立国') as V21 select dbo.[f_getpy_V1]('重复') as V21 select dbo.[f_getpy_V2]('重复') as V22 --运行结果 /* V11 -------- WLG V21 -------- WLG V21 -------- ZF V22 -------- ZF */ --由上我们可以看到，两种方法都没有解决多音字的问题。
12、根据身份证得到生日函数
go --创建函数(函数来自csdn，作者不详) create function [dbo].[Get_birthday] ( @idcardno nvarchar(50) ) returns varchar(10) as begin declare @birthday datetime if (len(@idcardno)=15 or len(@idcardno)=16) and substring(@idcardno,9,2) between 1 and 12 and substring(@idcardno,11,2) between 1 and 31 set @birthday= convert(varchar(10),'19'+substring(@idcardno,7,2)+'-'+substring(@idcardno,9,2)+'-'+substring(@idcardno,11,2),120) else if len(@idcardno)=18 and substring(@idcardno,7,2)>=19 and substring(@idcardno,11,2) between 1 and 12 and substring(@idcardno,13,2) between 1 and 31 set @birthday= convert(varchar(10),substring(@idcardno,7,4)+'-'+substring(@idcardno,11,2)+'-'+substring(@idcardno,13,2),120) else set @birthday=null return(convert(varchar(10),@birthday,120)) end --测试示例 select dbo.[Get_birthday]('222222198306043213') --运行结果 /* 1983-06-04 */
13、根据身份证计算性别函数
Go --创建函数(函数来自csdn，作者不详) create function [dbo].[Get_sex] ( @idcardno nvarchar(50) ) returns int as begin declare @sex int if (len(@idcardno)=18 and isnumeric(substring(@idcardno,17,1))=1 ) set @sex= (case when substring(@idcardno,17,1) in(1,3,5,7,9) then 1 when substring(@idcardno,17,1) in(2,4,6,7,0) then 2 else 0 end) else if (len(@idcardno)=15 and isnumeric(substring(@idcardno,15,1))=1 ) set @sex= (case when substring(@idcardno,15,1) in(1,3,5,7,9) then 1 when substring(@idcardno,15,1) in(2,4,6,7,0) then 2 else 0 end) else set @sex=0 return(@sex) end --测试示例 select dbo.[Get_sex]('222222198306043213') --运行结果(1表示男0表示女) /* 1 */
14、将身份证的15位号码升级为18位
go --创建函数(此函数来自于csdn，作者不详) create function [dbo].[id15to18] (@id15 char(15)) returns char(18) as begin declare @id18 char(18) declare @s1 as integer declare @s2 as integer declare @s3 as integer declare @s4 as integer declare @s5 as integer declare @s6 as integer declare @s7 as integer declare @s8 as integer declare @s9 as integer declare @s10 as integer declare @s11 as integer declare @s12 as integer declare @s13 as integer declare @s14 as integer declare @s15 as integer declare @s16 as integer declare @s17 as integer declare @s18 as integer set @s1=substring(@id15,1,1) set @s2=substring(@id15,2,1) set @s3=substring(@id15,3,1) set @s4=substring(@id15,4,1) set @s5=substring(@id15,5,1) set @s6=substring(@id15,6,1) set @s7=1 set @s8=9 set @s9=substring(@id15,7,1) set @s10=substring(@id15,8,1) set @s11=substring(@id15,9,1) set @s12=substring(@id15,10,1) set @s13=substring(@id15,11,1) set @s14=substring(@id15,12,1) set @s15=substring(@id15,13,1) set @s16=substring(@id15,14,1) set @s17=substring(@id15,15,1) set @s18=((@s1*7)+(@s2*9)+(@s3*10)+(@s4*5)+(@s5*8)+(@s6*4)+(@s7*2)+(@s8*1) +(@s9*6)+(@s10*3)+(@s11*7)+(@s12*9)+(@s13*10)+(@s14*5)+(@s15*8)+(@s16 *4)+(@s17*2))%11 set @id18=substring(@id15,1,6)+'19'+substring(@id15,7,9) +case when @s18=0 then '1' when @s18=1 then '0' when @s18=2 then 'x' when @s18=3 then '9' when @s18=4 then '8' when @s18=5 then '7' when @s18=6 then '6' when @s18=7 then '5' when @s18=8 then '4' when @s18=9 then '3' when @s18=10 then '2' end return @id18 end --测试示例 select [dbo].[id15to18]('222222830604321') --运行结果 /* 222222198306043213 */
15、通过身份证获得户籍
go --创建函数(此函数来自于csdn，作者不详) create function [dbo].[f_getcityfromcid] (@cid varchar(18)) returns varchar(50) as begin declare @acity varchar(1000) set @acity = '____,____,____,____,____,____,____,____,____,____,____,北京__,天津__,河北__,山西__,内蒙古_,____,____,____,____,____,辽宁__,吉林__,黑龙江_,____,____,____,____,____,____,____,上海__,江苏__,浙江__,安微__,福建__,江西__,山东__,____,____,____,河南__,湖北__,湖南__,广东__,广西__,海南__,____,____,____,重庆__,四川__,贵州__,云南__,西藏__,____,____,____,____,____,____,陕西__,甘肃__,青海__,宁夏__,新疆__,____,____,____,____,____,台湾__,____,____,____,____,____,____,____,____,____,香港__,澳门__,____,____,____,____,____,____,____,____,国外__,' select @acity=replace(@acity,' ',''); set @cid = upper(@cid) if (len(@cid) <> 18 or patindex('%[^0-9x]%',@cid) > 0) return '这不是合法的身份证' if substring(@acity,cast(left(@cid,2) as int)* 5+1,4) = '' return '这身份证的地区码不存在' return '您的户籍是：'+replace(substring(@acity,cast(left(@cid,2) as int)* 5+1,4),'_','') end --测试示例 select dbo.[f_getcityfromcid]('222222198306043213') --运行结果 /* 您的户籍是：吉林 */
16、多个数据项的字符串取指定位置字符
-- Author: happyflsytone -- Date:2008-11-05 14:59:34 -- 创建函数 create function [dbo].[split_str] ( @s varchar(8000), --包含多个数据项的字符串 @index int, --要获取的数据项的位置 @split varchar(10) --数据分隔符 ) returns varchar(100) as begin if @s is null return(null) begin declare @splitlen int select @splitlen=len(@split+'A')-2 end while @index>1 and charindex(@split,@s+@split)>0 begin select @index=@index-1,@s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'') end return(isnull(left(@s,charindex(@split,@s+@split)-1),'')) end --测试示例 select dbo.split_str('1-2-3-4',3,'-') --运行结果 /* 3 */
17、中缀算术转后缀算术表达式并计算的函数
--创建函数(原创：cson_cson) create function dbo.js(@bds varchar(1000)) returns float as begin declare @i int,@j int declare @c1 char(1),@c2 char(1),@c varchar(100) declare @v1 float,@v2 float,@v float declare @t table(id int identity(1,1),s varchar(100)) declare @s table(id int identity(1,1),s varchar(100)) declare @sv table(id int identity(1,1),v float) select @i = 0,@j = len(@bds),@c2 = '',@c = '' while @i<@j begin select @c1 = @c2,@i = @i+1 select @c2 = substring(@bds,@i,1) if charindex(@c2,'.0123456789') > 0 or (@c2 = '-' and @c1 in('','*','-','+','/','(')) begin select @c = @c + @c2 continue end if @c <> '' begin insert @t(s) select @c select @c = '' end if charindex(@c2,')')>0 begin insert @t(s) select s from @s where id > isnull((select max(id) from @s where s in('(')),0) order by id desc delete @s where id >= isnull((select max(id) from @s where s in('(')),0) continue end if charindex(@c2,'+-)')>0 begin insert @t(s) select s from @s where id > isnull((select max(id) from @s where s in('(')),0) order by id desc delete @s where id > isnull((select max(id) from @s where s in('(')),0) if @c2 <> ')' insert @s(s) select @c2 continue end if charindex(@c2,'*/')>0 begin insert @t(s) select s from @s where id > isnull((select max(id) from @s where s in('(','+','-')),0) order by id desc delete @s where id > isnull((select max(id) from @s where s in('(','+','-')),0) insert @s select @c2 continue end if charindex(@c2,'(')>0 insert @s select @c2 end if @c <> '' insert @t(s) select @c insert @t(s) select s from @s order by id desc select @i = 0,@j = max(id) from @t while @i < @j begin select @i = @i + 1 select @c = s from @t where id = @i if @c = '(' continue if @c not in('*','-','+','/') begin insert @sv(v) select convert(float,@c) continue end select @v2 = v from @sv delete @sv where id = (select max(id) from @sv) select @v1 = v from @sv delete @sv where id = (select max(id) from @sv) select @v = case @c when '+' then @v1 + @v2 when '-' then @v1 - @v2 when '*' then @v1 * @v2 when '/' then @v1 / @v2 end insert @sv(v) select @v end select @v = v from @sv return @v end --测试示例 declare @bds varchar(100) select @bds = '1+(2+3)*4-9' select dbo.js(@bds) as func --运行结果 /* func ---------------------- 12 */
18、人民币小写金额转大写
--1、人民币小写金额转大写(第一版) ==================================================================== -- Title: 人民币小写金额转大写 -- Author: dobear Mail(MSN): dobear_0922@hotmail.com -- Environment: Vista + SQL2005 -- Date: 2008-06-12 -- Remark: dobear原创，转载请注明出处，有问题请发Mail告之 -- ==================================================================== go --创建函数 create function [dbo].[fn_getformatmoney] (@money numeric(14,2)) returns nvarchar(32) as begin declare @money_num nvarchar(20) --存储金额的字符形式 , @money_chn nvarchar(32) --存储金额的中文大写形式 , @n_chn nvarchar(1), @i int --临时变量 select @money_chn=case when @money>=0 then '' else '(负)' end , @money=abs(@money) , @money_num=stuff(str(@money, 15, 2), 13, 1, '') --加前置空格补齐到位（去掉小数点） , @i=patindex('%[1-9]%', @money_num) --找到金额最高位 while @i>=1 and @i<=14 begin set @n_chn=substring(@money_num, @i, 1) if @n_chn<>'0' or (substring(@money_num,@i+1,1)<>'0' and @i not in(4, 8, 12, 14)) --转换阿拉伯数字为中文大写形式 set @money_chn=@money_chn+substring('零壹贰叁肆伍陆柒捌玖', @n_chn+1, 1) if @n_chn<>'0' or @i in(4, 8, 12) --添加中文单位 set @money_chn=@money_chn+substring('仟佰拾亿仟佰拾万仟佰拾圆角分',@i,1) set @i=@i+1 end set @money_chn=replace(@money_chn, '亿万', '亿') --当金额为x亿零万时去掉万 if @money=0 set @money_chn='零圆整' --当金额为零时返回'零圆整' if @n_chn='0' set @money_chn=@money_chn+'整' --当金额末尾为零分时以'整'结尾 return @money_chn --返回大写金额 end go --测试示例 select dbo.fn_getformatmoney(4545.44) --运行结果 /* 肆仟伍佰肆拾伍圆肆角肆分 */ --2、人民币小写金额转大写(第二版) /*编写者：(博客天地www.inbaidu.com ) 创建时间： 功能：小写金额转换成大写 参数：@LowerMoney 小写金额加上小数点最长可以保留位 输出：大写金额 简介：SQL版小写金额转换成大写金额（最多可以精确到小数点四位） 注： Decimal 数据类型最多可存储个数字 转载：请保留以上信息,谢谢！！！ ********************************************************/ go --创建函数 create function [dbo].[lowertoupper](@lowermoney decimal(38,4)) returns varchar(200) --返回的大写金额的字符 as begin declare @lowerstr varchar(50) --小写金额 declare @upperstr varchar(200) --大写金额 declare @uppertmp varchar(15) --大写金额的临时字符串 declare @i int --递增量 declare @lowerlen int --小写金额的总长度 set @lowerstr = @lowermoney --把decimal型的值全部赋给字符串变量注:(赋值过去的话如在字符串变量中是显示.0000 因为小数位精确到四位,没有的话，它会自动补) set @lowerstr = replace(@lowerstr,'.','') --把小数点替换成空字符 --精确到小数点的四位角分厘毫 set @lowerlen = len(@lowerstr) --获取小写金额的总长度(包括四个小数位) select @i = 1,@upperstr = '',@uppertmp = '' --设置默认初始值 while @i <= @lowerlen begin set @uppertmp = case when substring(@lowerstr,@lowerlen - @i + 1,1) = '0' and @i = 5 and (convert(int,right(@lowerstr,4)) = 0 or @lowerlen > 5) then '元' --注：如果个位为的话,并且四位小数都是或者它的长度超过(也就是超过元)，则为元 --when substring(@lowerstr,@lowerlen - @i + 1,1) = '0' then '' else + case substring(@lowerstr,@lowerlen - @i + 1,1) --看当前位是数字几,就直接替换成汉字繁体大写 when '0' then '零' when '1' then '壹' when '2' then '贰' when '3' then '叁' when '4' then '肆' when '5' then '伍' when '6' then '陆' when '7' then '柒' when '8' then '捌' when '9' then '玖' end + case @i when 1 then '毫' when 2 then '厘' when 3 then '分' when 4 then '角' when 5 then '元' when 9 then '萬' when 13 then '亿' when 17 then '兆' when 21 then '京' when 25 then '垓' when 29 then '杼' when 33 then '穰' when 37 then '沟' --decimal型最大长度是后面的就不用再考虑了 else + case @i%4 when 2 then '拾' --拾10 14 18 22 26 30 34 38 ………… when 3 then '佰' --佰11 15 19 23 27 31 35 39 ………… when 0 then '仟' --仟12 16 20 24 28 32 36 40 ………… end end end set @upperstr = isnull(@uppertmp,'') + isnull(@upperstr,'') set @i = @i + 1 end if convert(int,right(@lowerstr,4)) = 0 set @upperstr = left(@upperstr,len(@upperstr)-8) + '整' --判断小数位数是不是都是,是就可以取整 while patindex('%零[仟佰拾角分厘毫零]%',@upperstr) <> 0 --把零拾或零佰或零零变成一个零 begin set @upperstr = stuff(@upperstr,patindex('%零[仟佰拾角分厘毫零]%',@upperstr),2,'零') end while patindex('%[沟穰杼垓京兆亿萬]零[沟穰杼垓京兆亿萬]%',@upperstr) <> 0 --把零萬或零亿的清空掉 begin select @upperstr = stuff(@upperstr,patindex('%[沟穰杼垓京兆亿萬]零[沟穰杼垓京兆亿萬]%',@upperstr)+1,2,'') end while patindex('%[仟佰拾]零[沟穰杼垓京兆亿萬]%',@upperstr) <> 0 --把类似拾零萬或佰零萬或仟零萬中间的零清空掉 begin select @upperstr = stuff(@upperstr,patindex('%[仟佰拾]零[沟穰杼垓京兆亿萬]%',@upperstr)+1,1,'') end if patindex('%_零[元]%',@upperstr) <> 0 --把类似拾零元或百零元中间的零清空掉 begin select @upperstr = stuff(@upperstr,patindex('%_零[元]%',@upperstr) + 1,1,'') end else if (patindex('零[元]%',@upperstr) <> 0) and (convert(int,right(@lowerstr,4)) <> 0) --判断当前否是零元开头，并且后面的四个小数不为 begin select @upperstr = stuff(@upperstr,patindex('零[元]%',@upperstr),2,'') --把零元清空掉 end if right(@upperstr,1) = '零' set @upperstr = left(@upperstr,len(@upperstr)-1) --如果最后一位是零也清空掉 if @upperstr = '元整' set @upperstr = '零' + @upperstr --如果只是的话，就显示零元整 return @upperstr --返回大写金额 end --测试示例 Select dbo.LowerToUpper(120000000) --壹亿贰仟萬元整 Select dbo.LowerToUpper(102000000) --壹亿零贰佰萬元整 Select dbo.LowerToUpper(100200000) --壹亿零贰拾萬元整 Select dbo.LowerToUpper(100020000) --壹亿零贰萬元整 Select dbo.LowerToUpper(100002000) --壹亿贰仟元整 --运行结果 /* 壹亿贰仟萬元整 壹亿零贰佰萬元整 壹亿零贰拾萬元整 壹亿零贰萬元整 壹亿贰仟元整 */
19、向左填充指定字符串
go --创建函数(该函数来自csdn，作者不详) create function [dbo].[padleft] ( @str varchar(50), --需要填充的字符串 @totalwidth int, --填充后的长度 @paddingchar char(1)--填充使用的字符 ) returns varchar(1000) as begin declare @s varchar(100) set @s = @str if ( len(@str) < @totalwidth) begin declare @i int declare @strlen int declare @temp varchar(100) set @i = 1; set @strlen = @totalwidth - len(@str) set @temp = ''; while(@i <= @strlen ) begin set @temp = @temp + @paddingchar; set @i = @i + 1; end set @s = @temp + @str end return (@s) end go --测试示例 declare @table table (id nvarchar(20)) insert into @table select '1' union all select '2' union all select '3' union all select '4' union all select '5' union all select '6' select dbo.padleft(id,2,'0') as id from @table --运行结果 /* id ------- 01 02 03 04 05 06 */ go --创建函数(第二版)(作者：maco_wang) create function padleftV2 ( @sql varchar(200), --需填充的字符串 @char varchar(4), --填充使用的字符 @len int --填充后的长度 ) returns varchar(200) as begin return (right(replicate(@char,@len)+@sql,@len)) end go --测试示例 declare @table table(id int) insert into @table(id) select 1 union all select 3 union all select 6 select dbo.padleftV2(cast(id as varchar),'0',10) as id from @table --运行结果 /* id ------------- 0000000001 0000000003 0000000006 */
20、将整型数字转换为大写汉字
go --创建函数(该函数来自csdn,作者不详) create function [dbo].[m_NumToChinese](@num bigint) returns varchar(20) as begin declare @result varchar(20),@symbol varchar(2) if @num<0 select @symbol='负',@result='',@num=abs(@num) else select @symbol='',@result='' while @num<>0 select @result=substring('零壹贰叁肆伍陆柒捌玖拾',@num%10+1,1)+@result,@num=@num/10 return @symbol+@result end --测试示例 select dbo.[m_NumToChinese](12345678) --运行结果 /* 壹贰叁肆伍陆柒捌 */
21、检查给定串是否存在于由区间及点集的结合内
------------------------------------ -- Author: happyflystone -- Date:2009-07-20 -- Parameter: @CardString -- 被查询的串，形如：-13300001234,13300002230,13300002300 -- @CardNo 要查询的串 -- Return : int 0 -- 不存在于搜索串的范围内 -- 1 -- 存在于 -- 转载请注明出处。更多请访问：http://blog.csdn.net/happyflystone -- 原帖地址：http://blog.csdn.net/happyflystone/archive/2009/07/21/4365264.aspx ------------------------------------ --创建函数 Create function IsInCardString(@CardString varchar(8000),@CardNo varchar(11)) returns int as begin declare @temp table(a varchar(200)) declare @i int set @CardString = rtrim(ltrim(@CardString))+',' set @i = charindex(',', @CardString) while @i >= 1 begin insert @temp values(left(@CardString, @i - 1)) set @CardString = substring(@CardString, @i + 1, len(@CardString) - @i) set @i = charindex(',', @CardString) end if exists(select 1 from ( select case when charindex('-',a) > 0 then left(a,11) else a end as s, case when charindex('-',a) > 0 then right(a,11) else a end as e from @temp ) a where @CardNo between s and e) set @i= 1 else set @i= 0 return @i end go --测试示例 declare @CardString varchar(1000) set @CardString ='13300000000-13300001234,13300002230,13300002300,13300002302,13300004101-13300004204,13300004212,13300004310' declare @CardNo varchar(1000) set @CardNo = '13300000001' --存在 select dbo.IsInCardString(@CardString,@CardNo) as result1 set @CardNo = '13300001235' --不存在 select dbo.IsInCardString(@CardString,@CardNo) as result2 --运行结果 /* result1 ----------- 1 result2 ----------- 0 */
22、根据日期返回星座
go --创建函数(CSDN fredrickhu(小F)提供) create function udf_GetStar (@ datetime) RETURNS varchar(100) -- 返回日期所属星座 BEGIN RETURN ( select max(star) from ( select '魔羯座' as star,1 as [month],1 as [day] union all select '水瓶座',1,20 union all select '双鱼座',2,19 union all select '牡羊座',3,21 union all select '金牛座',4,20 union all select '双子座',5,21 union all select '巨蟹座',6,22 union all select '狮子座',7,23 union all select '处女座',8,23 union all select '天秤座',9,23 union all select '天蝎座',10,24 union all select '射手座',11,22 union all select '魔羯座',12,22 ) stars where dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1 = ( select max(dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1) from ( select '魔羯座' as star,1 as [month],1 as [day] union all select '水瓶座',1,20 union all select '双鱼座',2,19 union all select '牡羊座',3,21 union all select '金牛座',4,20 union all select '双子座',5,21 union all select '巨蟹座',6,22 union all select '狮子座',7,23 union all select '处女座',8,23 union all select '天秤座',9,23 union all select '天蝎座',10,24 union all select '射手座',11,22 union all select '魔羯座',12,22 ) stars where @ >= dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1 ) ) end --测试示例 select dbo.udf_GetStar('2010-05-04') select dbo.udf_GetStar('2009-01-04') select dbo.udf_GetStar('2007-12-04') --运行结果 /* 金牛座 魔羯座 射手座 */
23、计算两个日期之间的工作日
go --创建函数(CSDN fredrickhu(小F)提供) CREATE FUNCTION f_WorkDay ( @dt_begin datetime, --计算的开始日期 @dt_end datetime --计算的结束日期 )RETURNS int AS BEGIN DECLARE @workday int,@i int,@bz bit,@dt datetime IF @dt_begin> @dt_end SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt ELSE SET @bz=0 SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1, @workday=@i/7*5, @dt_begin=DATEADD(Day,@i/7*7,@dt_begin) WHILE @dt_begin <=@dt_end BEGIN SELECT @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5 THEN @workday+1 ELSE @workday END, @dt_begin=@dt_begin+1 END RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END) END GO --测试示例 select dbo.f_WorkDay('2011-02-22','2011-03-14') as '工作日' --运行结果 /* 工作日 ----------- 15 */
24、根据年月生成日历函数
go --创建函数(第一版)(作者：dobear_0922) create function fn_Calendar(@year int, @month int) returns nvarchar(max) as begin declare @result nvarchar(max), @Enter nvarchar(8) select @Enter = char(13)+char(10), @result = ' Sun Mon Tue Wed Thu Fri Sat' + @Enter --表头 declare @start datetime, @end datetime select @start = rtrim(@year)+'-'+rtrim(@month)+'-1', @end = dateadd(mm, 1, @start) set @result = @result+replicate(' ', (datepart(dw, @start)+@@datefirst+6)%7) --第一行前面的空格 while datediff(d, @start, @end)>0 begin if (datepart(dw, @start)+@@datefirst)%7 = 1 select @result = @result+@Enter --是否换行 select @result = @result+right(' '+rtrim(day(@start)), 4), @start = dateadd(d, 1, @start) end return @result end go --测试示例 set datefirst 3 print dbo.fn_Calendar(2007, 12) select dbo.fn_Calendar(2007, 12) set datefirst 7 --运行结果 /* Sun Mon Tue Wed Thu Fri Sat 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 */ go --创建函数(第二版)(作者：libin_ftsafe) create function f_calendar(@year int,@month int) returns @t table(日varchar(4),一varchar(4),二varchar(4),三varchar(4),四varchar(4),五varchar(4),六varchar(4)) as begin declare @a table(id int identity(0,1),date datetime) insert into @a(date) select top 31 rtrim(@year)+'-'+rtrim(@month)+'-1' from sysobjects update @a set date=dateadd(dd,id,date) insert into @t select max(case datepart(dw,date) when 7 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 1 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 2 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 3 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 4 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 5 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 6 then rtrim(day(date)) else '' end) from @a where month(date)=@month group by (case datepart(dw,date) when 7 then datepart(week,date)+1 else datepart(week,date) end) return end go --测试示例 set datefirst 1 select * from dbo.f_calendar(2007,12) --运行结果 /* 日 一 二 三 四 五 六 ---- ---- ---- ---- ---- ---- ---- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 */
25、从第一个汉字开始分割字符串
go --创建函数 create function [dbo].[m_splitNchinese] ( @str_one nvarchar(100) ) returns @result table (colone nvarchar(20),coltwo nvarchar(20)) as begin insert @result select left(@str_one,patindex('%[^_@0-9a-z]%',@str_one)-1) , right(@str_one,len(@str_one)-patindex('%[^_@0-9a-z]%',@str_one)+1) return end --测试示例 select * from [dbo].[m_splitNchinese] ('Chinese中国') --运行结果 /* colone coltwo -------------------- -------------------- Chinese 中国 */
26、过滤掉字符串中重复的字符
go --创建函数(此函数来自csdn，作者不详) create function [dbo].[m_distinctStr](@s varchar(max)) returns varchar(100) as begin if @s is null return(null) declare @new varchar(50),@index int,@temp varchar(50) while len(@s)>0 begin set @new=isnull(@new,'')+left(@s,1) set @s=replace(@s,left(@s,1),'') end return @new end --测试示例 select dbo.[m_distinctStr]('Chinese') as str1 select dbo.[m_distinctStr]('张三李四李四张三刘六') as str2 --运行结果结果 /* str1 --------- Chines str2 ------------ 张三李四刘六 */
27、根据日期得到星期的函数
--最直接的方式就是用case when判断，这里用表变量来处理的 go --创建函数 create function [dbo].[m_getweek](@date nvarchar(2000)) returns varchar(2000) AS begin declare @weekday nvarchar(300) declare @table table (id int ,weekday nvarchar(200)) insert into @table select 0,'星期天' union select 1,'星期一' union select 2,'星期二' union select 3,'星期三' union select 4,'星期四' union select 5,'星期五' union select 6,'星期六' select @weekday=weekday from @table where id=(datepart(dw,@date)-1) return @weekday end --测试示例 select [dbo].[m_getweek](getdate()) as 星期 --今天的星期 /* 星期 -------- 星期六 */
28、根据年度判断是否是闰年
--创建函数 create function [dbo].[fn_IsLeapYear] ( @year int ) returns varchar(14) as begin declare @returnvalue varchar(14) declare @setvalue int set @setvalue=datepart(mm,dateadd(dd,1,cast((cast(@year as varchar(4))+ '0228') as datetime))) if(@setvalue=2) set @returnvalue='闰年' else set @returnvalue='非闰年' return (cast (@year as varchar(8))+'年:'+@returnvalue) end go --测试示例 select dbo.[fn_IsLeapYear] (year(getdate())) select dbo.[fn_IsLeapYear] (2008) --运行结果 /* 2011年:非闰年 2008年:闰年 */
29、完善SQL农历转换函数
-------------------------------------------------------------------- -- Author : 原著： 改编：htl258(Tony) -- Date : 2010-04-24 06:39:55 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Blog : http://blog.csdn.net/htl258 -- 原帖地址： -- http://blog.csdn.net/htl258/archive/2010/04/24/5523183.aspx -- Subject: 完善SQL农历转换函数（显示中文格式，加入润月的显示） -------------------------------------------------------------------------- --注：由于找一时找不到原版作者，所以暂未填入。大家有知道的告知一下，谢谢！ --创建基础数据表 if object_id('SolarData') is not null drop table SolarData go create table SolarData ( yearid int not null, data char(7) not null, dataint int not null ) --插入数据 insert into SolarData select 1900,'0x04bd8',19416 union all select 1901,'0x04ae0',19168 union all select 1902,'0x0a570',42352 union all select 1903,'0x054d5',21717 union all select 1904,'0x0d260',53856 union all select 1905,'0x0d950',55632 union all select 1906,'0x16554',91476 union all select 1907,'0x056a0',22176 union all select 1908,'0x09ad0',39632 union all select 1909,'0x055d2',21970 union all select 1910,'0x04ae0',19168 union all select 1911,'0x0a5b6',42422 union all select 1912,'0x0a4d0',42192 union all select 1913,'0x0d250',53840 union all select 1914,'0x1d255',119381 union all select 1915,'0x0b540',46400 union all select 1916,'0x0d6a0',54944 union all select 1917,'0x0ada2',44450 union all select 1918,'0x095b0',38320 union all select 1919,'0x14977',84343 union all select 1920,'0x04970',18800 union all select 1921,'0x0a4b0',42160 union all select 1922,'0x0b4b5',46261 union all select 1923,'0x06a50',27216 union all select 1924,'0x06d40',27968 union all select 1925,'0x1ab54',109396 union all select 1926,'0x02b60',11104 union all select 1927,'0x09570',38256 union all select 1928,'0x052f2',21234 union all select 1929,'0x04970',18800 union all select 1930,'0x06566',25958 union all select 1931,'0x0d4a0',54432 union all select 1932,'0x0ea50',59984 union all select 1933,'0x06e95',28309 union all select 1934,'0x05ad0',23248 union all select 1935,'0x02b60',11104 union all select 1936,'0x186e3',100067 union all select 1937,'0x092e0',37600 union all select 1938,'0x1c8d7',116951 union all select 1939,'0x0c950',51536 union all select 1940,'0x0d4a0',54432 union all select 1941,'0x1d8a6',120998 union all select 1942,'0x0b550',46416 union all select 1943,'0x056a0',22176 union all select 1944,'0x1a5b4',107956 union all select 1945,'0x025d0',9680 union all select 1946,'0x092d0',37584 union all select 1947,'0x0d2b2',53938 union all select 1948,'0x0a950',43344 union all select 1949,'0x0b557',46423 union all select 1950,'0x06ca0',27808 union all select 1951,'0x0b550',46416 union all select 1952,'0x15355',86869 union all select 1953,'0x04da0',19872 union all select 1954,'0x0a5d0',42448 union all select 1955,'0x14573',83315 union all select 1956,'0x052d0',21200 union all select 1957,'0x0a9a8',43432 union all select 1958,'0x0e950',59728 union all select 1959,'0x06aa0',27296 union all select 1960,'0x0aea6',44710 union all select 1961,'0x0ab50',43856 union all select 1962,'0x04b60',19296 union all select 1963,'0x0aae4',43748 union all select 1964,'0x0a570',42352 union all select 1965,'0x05260',21088 union all select 1966,'0x0f263',62051 union all select 1967,'0x0d950',55632 union all select 1968,'0x05b57',23383 union all select 1969,'0x056a0',22176 union all select 1970,'0x096d0',38608 union all select 1971,'0x04dd5',19925 union all select 1972,'0x04ad0',19152 union all select 1973,'0x0a4d0',42192 union all select 1974,'0x0d4d4',54484 union all select 1975,'0x0d250',53840 union all select 1976,'0x0d558',54616 union all select 1977,'0x0b540',46400 union all select 1978,'0x0b5a0',46496 union all select 1979,'0x195a6',103846 union all select 1980,'0x095b0',38320 union all select 1981,'0x049b0',18864 union all select 1982,'0x0a974',43380 union all select 1983,'0x0a4b0',42160 union all select 1984,'0x0b27a',45690 union all select 1985,'0x06a50',27216 union all select 1986,'0x06d40',27968 union all select 1987,'0x0af46',44870 union all select 1988,'0x0ab60',43872 union all select 1989,'0x09570',38256 union all select 1990,'0x04af5',19189 union all select 1991,'0x04970',18800 union all select 1992,'0x064b0',25776 union all select 1993,'0x074a3',29859 union all select 1994,'0x0ea50',59984 union all select 1995,'0x06b58',27480 union all select 1996,'0x055c0',21952 union all select 1997,'0x0ab60',43872 union all select 1998,'0x096d5',38613 union all select 1999,'0x092e0',37600 union all select 2000,'0x0c960',51552 union all select 2001,'0x0d954',55636 union all select 2002,'0x0d4a0',54432 union all select 2003,'0x0da50',55888 union all select 2004,'0x07552',30034 union all select 2005,'0x056a0',22176 union all select 2006,'0x0abb7',43959 union all select 2007,'0x025d0',9680 union all select 2008,'0x092d0',37584 union all select 2009,'0x0cab5',51893 union all select 2010,'0x0a950',43344 union all select 2011,'0x0b4a0',46240 union all select 2012,'0x0baa4',47780 union all select 2013,'0x0ad50',44368 union all select 2014,'0x055d9',21977 union all select 2015,'0x04ba0',19360 union all select 2016,'0x0a5b0',42416 union all select 2017,'0x15176',86390 union all select 2018,'0x052b0',21168 union all select 2019,'0x0a930',43312 union all select 2020,'0x07954',31060 union all select 2021,'0x06aa0',27296 union all select 2022,'0x0ad50',44368 union all select 2023,'0x05b52',23378 union all select 2024,'0x04b60',19296 union all select 2025,'0x0a6e6',42726 union all select 2026,'0x0a4e0',42208 union all select 2027,'0x0d260',53856 union all select 2028,'0x0ea65',60005 union all select 2029,'0x0d530',54576 union all select 2030,'0x05aa0',23200 union all select 2031,'0x076a3',30371 union all select 2032,'0x096d0',38608 union all select 2033,'0x04bd7',19415 union all select 2034,'0x04ad0',19152 union all select 2035,'0x0a4d0',42192 union all select 2036,'0x1d0b6',118966 union all select 2037,'0x0d250',53840 union all select 2038,'0x0d520',54560 union all select 2039,'0x0dd45',56645 union all select 2040,'0x0b5a0',46496 union all select 2041,'0x056d0',22224 union all select 2042,'0x055b2',21938 union all select 2043,'0x049b0',18864 union all select 2044,'0x0a577',42359 union all select 2045,'0x0a4b0',42160 union all select 2046,'0x0aa50',43600 union all select 2047,'0x1b255',111189 union all select 2048,'0x06d20',27936 union all select 2049,'0x0ada0',44448 GO --=============================================================== --创建农历日期函数 if object_id('fn_GetLunar') is not null drop function fn_GetLunar go create function dbo.fn_GetLunar(@solarday datetime) returns nvarchar(30) as begin declare @soldata int declare @offset int declare @ilunar int declare @i int declare @j int declare @ydays int declare @mdays int declare @mleap int declare @mleap1 int declare @mleapnum int declare @bleap smallint declare @temp int declare @year nvarchar(10) declare @month nvarchar(10) declare @day nvarchar(10) declare @chinesenum nvarchar(10) declare @outputdate nvarchar(30) set @offset=datediff(day,'1900-01-30',@solarday) --确定农历年开始 set @i=1900 --set @offset=@soldata while @i<2050 and @offset>0 begin set @ydays=348 set @mleapnum=0 select @ilunar=dataint from solardata where yearid=@i --传回农历年的总天数 set @j=32768 while @j>8 begin if @ilunar & @j >0 set @ydays=@ydays+1 set @j=@j/2 end --传回农历年闰哪个月1-12 , 没闰传回0 set @mleap = @ilunar & 15 --传回农历年闰月的天数,加在年的总天数上 if @mleap > 0 begin if @ilunar & 65536 > 0 set @mleapnum=30 else set @mleapnum=29 set @ydays=@ydays+@mleapnum end set @offset=@offset-@ydays set @i=@i+1 end if @offset <= 0 begin set @offset=@offset+@ydays set @i=@i-1 end --确定农历年结束 set @year=@i --确定农历月开始 set @i = 1 select @ilunar=dataint from solardata where yearid=@year --判断那个月是润月 set @mleap = @ilunar & 15 set @bleap = 0 while @i < 13 and @offset > 0 begin --判断润月 set @mdays=0 if (@mleap > 0 and @i = (@mleap+1) and @bleap=0) begin--是润月 set @i=@i-1 set @bleap=1 set @mleap1= @mleap --传回农历年闰月的天数 if @ilunar & 65536 > 0 set @mdays = 30 else set @mdays = 29 end else --不是润月 begin set @j=1 set @temp = 65536 while @j<=@i begin set @temp=@temp/2 set @j=@j+1 end if @ilunar & @temp > 0 set @mdays = 30 else set @mdays = 29 end --解除润月 if @bleap=1 and @i= (@mleap+1) set @bleap=0 set @offset=@offset-@mdays set @i=@i+1 end if @offset <= 0 begin set @offset=@offset+@mdays set @i=@i-1 end --确定农历月结束 set @month=@i --确定农历日结束 set @day=ltrim(@offset) --输出日期 set @chinesenum=N'〇一二三四五六七八九十' while len(@year)>0 select @outputdate=isnull(@outputdate,'') + substring(@chinesenum,left(@year,1)+1,1) , @year=stuff(@year,1,1,'') set @outputdate=@outputdate+N'年' + case @mleap1 when @month then N'润' else '' end if cast(@month as int)<10 set @outputdate=@outputdate + case @month when 1 then N'正' else substring(@chinesenum,left(@month,1)+1,1) end else if cast(@month as int)>=10 set @outputdate=@outputdate + case @month when '10' then N'十' when 11 then N'十一' else N'十二' end set @outputdate=@outputdate + N'月' if cast(@day as int)<10 set @outputdate=@outputdate + N'初' + substring(@chinesenum,left(@day,1)+1,1) else if @day between '10' and '19' set @outputdate=@outputdate + case @day when '10' then N'初十' else N'十'+ substring(@chinesenum,right(@day,1)+1,1) end else if @day between '20' and '29' set @outputdate=@outputdate + case @day when '20' then N'二十' else N'廿' end + case @day when '20' then N'' else substring(@chinesenum,right(@day,1)+1,1) end else set @outputdate=@outputdate+N'三十' return @outputdate end GO --测试示例 select dbo.fn_GetLunar(getdate()) as [改编日期(农历)], getdate() as [改编日期(公历)] /* 改编日期(农历) 改编日期(公历) ------------------------------ ----------------------- 二〇一一年二月十六 2011-03-20 11:24:35.577 */ select convert(char(10),dateadd(d,number,'2008-1-1'),23) as 公历, dbo.fn_GetLunar(dateadd(d,number,'2008-1-1')) as 农历 from master..spt_values where type='p' /* 公历 农历 ---------- ------------------------------ 2008-01-01 二〇〇七年十一月廿三 2008-01-02 二〇〇七年十一月廿四 2008-01-03 二〇〇七年十一月廿五 2008-01-04 二〇〇七年十一月廿六 2008-01-05 二〇〇七年十一月廿七 2008-01-06 二〇〇七年十一月廿八 2008-01-07 二〇〇七年十一月廿九 2008-01-08 二〇〇七年十二月初一 2008-01-09 二〇〇七年十二月初二 2008-01-10 二〇〇七年十二月初三 ............... 2013-07-31 二〇一三年六月廿四 2013-08-01 二〇一三年六月廿五 2013-08-02 二〇一三年六月廿六 2013-08-03 二〇一三年六月廿七 2013-08-04 二〇一三年六月廿八 2013-08-05 二〇一三年六月廿九 2013-08-06 二〇一三年六月三十 2013-08-07 二〇一三年七月初一 2013-08-08 二〇一三年七月初二 2013-08-09 二〇一三年七月初三 (2048 行受影响) */ --2048行记录：秒
30、SQL简繁转换函数
--原帖地址：http://blog.csdn.net/htl258/archive/2010/04/20/5506045.aspx --全部的简繁对照 declare @jall nvarchar(4000),@fall nvarchar(4000) select @jall=N'啊阿埃挨哎唉哀皑癌蔼矮艾碍爱隘鞍氨安俺按暗岸胺案肮昂盎凹敖熬翱袄傲奥懊澳芭捌扒叭吧笆八疤巴拔跋靶把耙坝霸罢爸白柏百摆佰败拜稗斑班搬扳般颁板版扮拌伴瓣半办绊邦帮梆榜膀绑棒磅蚌镑傍谤苞胞包褒剥薄雹保堡饱宝抱报暴豹鲍爆杯碑悲卑北辈背贝钡倍狈备惫焙被奔苯本笨崩绷甭泵蹦迸逼鼻比鄙笔彼碧蓖蔽毕毙毖币庇痹闭敝弊必辟壁臂避陛鞭边编贬扁便变卞辨辩辫遍标彪膘表鳖憋别瘪彬斌濒滨宾摈兵冰柄丙秉饼炳病并玻菠播拨钵波博勃搏铂箔伯帛舶脖膊渤泊驳捕卜哺补埠不布步簿部怖擦猜裁材才财睬踩采彩菜蔡餐参蚕残惭惨灿苍舱仓沧藏操糙槽曹草厕策侧册测层蹭插叉茬茶查碴搽察岔差诧拆柴豺搀掺蝉馋谗缠铲产阐颤昌猖场尝常长偿肠厂敞畅唱倡超抄钞朝嘲潮巢吵炒车扯撤掣彻澈郴臣辰尘晨忱沉陈趁衬撑称城橙成呈乘程惩澄诚承逞骋秤吃痴持匙池迟弛驰耻齿侈尺赤翅斥炽充冲虫崇宠抽酬畴踌稠愁筹仇绸瞅丑臭初出橱厨躇锄雏滁除楚础储矗搐触处揣川穿椽传船喘串疮窗幢床闯创吹炊捶锤垂春椿醇唇淳纯蠢戳绰疵茨磁雌辞慈瓷词此刺赐次聪葱囱匆从丛凑粗醋簇促蹿篡窜摧崔催脆瘁粹淬翠村存寸磋撮搓措挫错搭达答瘩打大呆歹傣戴带殆代贷袋待逮怠耽担丹单郸掸胆旦氮但惮淡诞弹蛋当挡党荡档刀捣蹈倒岛祷导到稻悼道盗德得的蹬灯登等瞪凳邓堤低滴迪敌笛狄涤翟嫡抵底地蒂第帝弟递缔颠掂滇碘点典靛垫电佃甸店惦奠淀殿碉叼雕凋***掉吊钓调跌爹碟蝶迭谍叠丁盯叮钉顶鼎锭定订丢东冬董懂动栋侗恫冻洞兜抖斗陡豆逗痘都督毒犊独读堵睹赌杜镀肚度渡妒端短锻段断缎堆兑队对墩吨蹲敦顿囤钝盾遁掇哆多夺垛躲朵跺舵剁惰堕蛾峨鹅俄额讹娥恶厄扼遏鄂饿恩而儿耳尔饵洱二贰发罚筏伐乏阀法珐藩帆番翻樊矾钒繁凡烦反返范贩犯饭泛坊芳方肪房防妨仿访纺放菲非啡飞肥匪诽吠肺废沸费芬酚吩氛分纷坟焚汾粉奋份忿愤粪丰封枫蜂峰锋风疯烽逢冯缝讽奉凤佛否夫敷肤孵扶拂辐幅氟符伏俘服浮涪福袱弗甫抚辅俯釜斧脯腑府腐赴副覆赋复傅付阜父腹负富讣附妇缚咐噶嘎该改概钙盖溉干甘杆柑竿肝赶感秆敢赣冈刚钢缸肛纲岗港杠篙皋高膏羔糕搞镐稿告哥歌搁戈鸽胳疙割革葛格蛤阁隔铬个各给根跟耕更庚羹埂耿梗工攻功恭龚供躬公宫弓巩汞拱贡共钩勾沟苟狗垢构购够辜菇咕箍估沽孤姑鼓古蛊骨谷股故顾固雇刮瓜剐寡挂褂乖拐怪棺关官冠观管馆罐惯灌贯光广逛瑰规圭硅归龟闺轨鬼诡癸桂柜跪贵刽辊滚棍锅郭国果裹过哈骸孩海氦亥害骇酣憨邯韩含涵寒函喊罕翰撼捍旱憾悍焊汗汉夯杭航壕嚎豪毫郝好耗号浩呵喝荷菏核禾和何合盒貉阂河涸赫褐鹤贺嘿黑痕很狠恨哼亨横衡恒轰哄烘虹鸿洪宏弘红喉侯猴吼厚候后呼乎忽瑚壶葫胡蝴狐糊湖弧虎唬护互沪户花哗华猾滑画划化话槐徊怀淮坏欢环桓还缓换患唤痪豢焕涣宦幻荒慌黄磺蝗簧皇凰惶煌晃幌恍谎灰挥辉徽恢蛔回毁悔慧卉惠晦贿秽会烩汇讳诲绘荤昏婚魂浑混豁活伙火获或惑霍货祸击圾基机畸稽积箕肌饥迹激讥鸡姬绩缉吉极棘辑籍集及急疾汲即嫉级挤几脊己蓟技冀季伎祭剂悸济寄寂计记既忌际妓继纪嘉枷夹佳家加荚颊贾甲钾假稼价架驾嫁歼监坚尖笺间煎兼肩艰奸缄茧检柬碱硷拣捡简俭剪减荐槛鉴践贱见键箭件健舰剑饯渐溅涧建僵姜将浆江疆蒋桨奖讲匠酱降蕉椒礁焦胶交郊浇骄娇嚼搅铰矫侥脚狡角饺缴绞剿教酵轿较叫窖揭接皆秸街阶截劫节茎睛晶鲸京惊精粳经井警景颈静境敬镜径痉靖竟竞净炯窘揪究纠玖韭久灸九酒厩救旧臼舅咎就疚鞠拘狙疽居驹菊局咀矩举沮聚拒据巨具距踞锯俱句惧炬剧捐鹃娟倦眷卷绢撅攫抉掘倔爵桔杰捷睫竭洁结解姐戒藉芥界借介疥诫届巾筋斤金今津襟紧锦仅谨进靳晋禁近烬浸尽劲荆兢觉决诀绝均菌钧军君峻俊竣浚郡骏喀咖卡咯开揩楷凯慨刊堪勘坎砍看康慷糠扛抗亢炕考拷烤靠坷苛柯棵磕颗科壳咳可渴克刻客课肯啃垦恳坑吭空恐孔控抠口扣寇枯哭窟苦酷库裤夸垮挎跨胯块筷侩快宽款匡筐狂框矿眶旷况亏盔岿窥葵奎魁傀馈愧溃坤昆捆困括扩廓阔垃拉喇蜡腊辣啦莱来赖蓝婪栏拦篮阑兰澜谰揽览懒缆烂滥琅榔狼廊郎朗浪捞劳牢老佬姥酪烙涝勒乐雷镭蕾磊累儡垒擂肋类泪棱楞冷厘梨犁黎篱狸离漓理李里鲤礼莉荔吏栗丽厉励砾历利傈例俐痢立粒沥隶力璃哩俩联莲连镰廉怜涟帘敛脸链恋炼练粮凉梁粱良两辆量晾亮谅撩聊僚疗燎寥辽潦了撂镣廖料列裂烈劣猎琳林磷霖临邻鳞淋凛赁吝拎玲菱零龄铃伶羚凌灵陵岭领另令溜琉榴硫馏留刘瘤流柳六龙聋咙笼窿隆垄拢陇楼娄搂篓漏陋芦卢颅庐炉掳卤虏鲁麓碌露路赂鹿潞禄录陆戮驴吕铝侣旅履屡缕虑氯律率滤绿峦挛孪滦卵乱掠略抡轮伦仑沦纶论萝螺罗逻锣箩骡裸落洛骆络妈麻玛码蚂马骂嘛吗埋买麦卖迈脉瞒馒蛮满蔓曼慢漫谩芒茫盲氓忙莽猫茅锚毛矛铆卯茂冒帽貌贸么玫枚梅酶霉煤没眉媒镁每美昧寐妹媚门闷们萌蒙檬盟锰猛梦孟眯醚靡糜迷谜弥米秘觅泌蜜密幂棉眠绵冕免勉娩缅面苗描瞄藐秒渺庙妙蔑灭民抿皿敏悯闽明螟鸣铭名命谬摸摹蘑模膜磨摩魔抹末莫墨默沫漠寞陌谋牟某拇牡亩姆母墓暮幕募慕木目睦牧穆拿哪呐钠那娜纳氖乃奶耐奈南男难囊挠脑恼闹淖呢馁内嫩能妮霓倪泥尼拟你匿腻逆溺蔫拈年碾撵捻念娘酿鸟尿捏聂孽啮镊镍涅您柠狞凝宁拧泞牛扭钮纽脓浓农弄奴努怒女暖虐疟挪懦糯诺哦欧鸥殴藕呕偶沤啪趴爬帕怕琶拍排牌徘湃派攀潘盘磐盼畔判叛乓庞旁耪胖抛咆刨炮袍跑泡呸胚培裴赔陪配佩沛喷盆砰抨烹澎彭蓬棚硼篷膨朋鹏捧碰坯砒霹批披劈琵毗啤脾疲皮匹痞僻屁譬篇偏片骗飘漂瓢票撇瞥拼频贫品聘乒坪苹萍平凭瓶评屏坡泼颇婆破魄迫粕剖扑铺仆莆葡菩蒲埔朴圃普浦谱曝瀑期欺栖戚妻七凄漆柒沏其棋奇歧畦崎脐齐旗祈祁骑起岂乞企启契砌器气迄弃汽泣讫掐洽牵扦钎铅千迁签仟谦乾黔钱钳前潜遣浅谴堑嵌欠歉枪呛腔羌墙蔷强抢橇锹敲悄桥瞧乔侨巧鞘撬翘峭俏窍切茄且怯窃钦侵亲秦琴勤芹擒禽寝沁青轻氢倾卿清擎晴氰情顷请庆琼穷秋丘邱球求囚酋泅趋区蛆曲躯屈驱渠取娶龋趣去圈颧权醛泉全痊拳犬券劝缺炔瘸却鹊榷确雀裙群然燃冉染瓤壤攘嚷让饶扰绕惹热壬仁人忍韧任认刃妊纫扔仍日戎茸蓉荣融熔溶容绒冗揉柔肉茹蠕儒孺如辱乳汝入褥软阮蕊瑞锐闰润若弱撒洒萨腮鳃塞赛三叁伞散桑嗓丧搔骚扫嫂瑟色涩森僧莎砂杀刹沙纱傻啥煞筛晒珊苫杉山删煽衫闪陕擅赡膳善汕扇缮墒伤商赏晌上尚裳梢捎稍烧芍勺韶少哨邵绍奢赊蛇舌舍赦摄射慑涉社设砷申呻伸身深娠绅神沈审婶甚肾慎渗声生甥牲升绳省盛剩胜圣师失狮施湿诗尸虱十石拾时什食蚀实识史矢使屎驶始式示士世柿事拭誓逝势是嗜噬适仕侍释饰氏市恃室视试收手首守寿授售受瘦兽蔬枢梳殊抒输叔舒淑疏书赎孰熟薯暑曙署蜀黍鼠属术述树束戍竖墅庶数漱恕刷耍摔衰甩帅栓拴霜双爽谁水睡税吮瞬顺舜说硕朔烁斯撕嘶思私司丝死肆寺嗣四伺似饲巳松耸怂颂送宋讼诵搜艘擞嗽苏酥俗素速粟僳塑溯宿诉肃酸蒜算虽隋随绥髓碎岁穗遂隧祟孙损笋蓑梭唆缩琐索锁所塌他它她塔獭挞蹋踏胎苔抬台泰酞太态汰坍摊贪瘫滩坛檀痰潭谭谈坦毯袒碳探叹炭汤塘搪堂棠膛唐糖倘躺淌趟烫掏涛滔绦萄桃逃淘陶讨套特藤腾疼誊梯剔踢锑提题蹄啼体替嚏惕涕剃屉天添填田甜恬舔腆挑条迢眺跳贴铁帖厅听烃汀廷停亭庭挺艇通桐酮瞳同铜彤童桶捅筒统痛偷投头透凸秃突图徒途涂屠土吐兔湍团推颓腿蜕褪退吞屯臀拖托脱鸵陀驮驼椭妥拓唾挖哇蛙洼娃瓦袜歪外豌弯湾玩顽丸烷完碗挽晚皖惋宛婉万腕汪王亡枉网往旺望忘妄威巍微危韦违桅围唯惟为潍维苇萎委伟伪尾纬未蔚味畏胃喂魏位渭谓尉慰卫瘟温蚊文闻纹吻稳紊问嗡翁瓮挝蜗涡窝我斡卧握沃巫呜钨乌污诬屋无芜梧吾吴毋武五捂午舞伍侮坞戊雾晤物勿务悟误昔熙析西硒矽晰嘻吸锡牺稀息希悉膝夕惜熄烯溪汐犀檄袭席习媳喜铣洗系隙戏细瞎虾匣霞辖暇峡侠狭下厦夏吓掀锨先仙鲜纤咸贤衔舷闲涎弦嫌显险现献县腺馅羡宪陷限线相厢镶香箱襄湘乡翔祥详想响享项巷橡像向象萧硝霄削哮嚣销消宵淆晓小孝校肖啸笑效楔些歇蝎鞋协挟携邪斜胁谐写械卸蟹懈泄泻谢屑薪芯锌欣辛新忻心信衅星腥猩惺兴刑型形邢行醒幸杏性姓兄凶胸匈汹雄熊休修羞朽嗅锈秀袖绣墟戌需虚嘘须徐许蓄酗叙旭序畜恤絮婿绪续轩喧宣悬旋玄选癣眩绚靴薛学穴雪血勋熏循旬询寻驯巡殉汛训讯逊迅压押鸦鸭呀丫芽牙蚜崖衙涯雅哑亚讶焉咽阉烟淹盐严研蜒岩延言颜阎炎沿奄掩眼衍演艳堰燕厌砚雁唁彦焰宴谚验殃央鸯秧杨扬佯疡羊洋阳氧仰痒养样漾邀腰妖瑶摇尧遥窑谣姚咬舀药要耀椰噎耶爷野冶也页掖业叶曳腋夜液一壹医揖铱依伊衣颐夷遗移仪胰疑沂宜姨彝椅蚁倚已乙矣以艺抑易邑屹亿役臆逸肄疫亦裔意毅忆义益溢诣议谊译异翼翌绎茵荫因殷音阴姻吟银淫寅饮尹引隐印英樱婴鹰应缨莹萤营荧蝇迎赢盈影颖硬映哟拥佣臃痈庸雍踊蛹咏泳涌永恿勇用幽优悠忧尤由邮铀犹油游酉有友右佑釉诱又幼迂淤于盂榆虞愚舆余俞逾鱼愉渝渔隅予娱雨与屿禹宇语羽玉域芋郁吁遇喻峪御愈欲狱育誉浴寓裕预豫驭鸳渊冤元垣袁原援辕园员圆猿源缘远苑愿怨院曰约越跃钥岳粤月悦阅耘云郧匀陨允运蕴酝晕韵孕匝砸杂栽哉灾宰载再在咱攒暂赞赃脏葬遭糟凿藻枣早澡蚤躁噪造皂灶燥责择则泽贼怎增憎曾赠扎喳渣札轧铡闸眨栅榨咋乍炸诈摘斋宅窄债寨瞻毡詹粘沾盏斩辗崭展蘸栈占战站湛绽樟章彰漳张掌涨杖丈帐账仗胀瘴障招昭找沼赵照罩兆肇召遮折哲蛰辙者锗蔗这浙珍斟真甄砧臻贞针侦枕疹诊震振镇阵蒸挣睁征狰争怔整拯正政帧症郑证芝枝支吱蜘知肢脂汁之织职直植殖执值侄址指止趾只旨纸志挚掷至致置帜峙制智秩稚质炙痔滞治窒中盅忠钟衷终种肿重仲众舟周州洲诌粥轴肘帚咒皱宙昼骤珠株蛛朱猪诸诛逐竹烛煮拄瞩嘱主著柱助蛀贮铸筑住注祝驻抓爪拽专砖转撰赚篆桩庄装妆撞壮状椎锥追赘坠缀谆准捉拙卓桌琢茁酌啄着灼浊兹咨资姿滋淄孜紫仔籽滓子自渍字鬃棕踪宗综总纵邹走奏揍租足卒族祖诅阻组钻纂嘴醉最罪尊遵昨左佐柞做作坐座' ,@fall=N'啊阿埃挨哎唉哀皚癌藹矮艾礙愛隘鞍氨安俺按暗岸胺案肮昂盎凹敖熬翺襖傲奧懊澳芭捌扒叭吧笆八疤巴拔跋靶把耙壩霸罷爸白柏百擺佰敗拜稗斑班搬扳般頒板版扮拌伴瓣半辦絆邦幫梆榜膀綁棒磅蚌鎊傍謗苞胞包褒剝薄雹保堡飽寶抱報暴豹鮑爆杯碑悲卑北輩背貝鋇倍狽備憊焙被奔苯本笨崩繃甭泵蹦迸逼鼻比鄙筆彼碧蓖蔽畢斃毖幣庇痹閉敝弊必辟壁臂避陛鞭邊編貶扁便變卞辨辯辮遍標彪膘表鼈憋別癟彬斌瀕濱賓擯兵冰柄丙秉餅炳病並玻菠播撥缽波博勃搏鉑箔伯帛舶脖膊渤泊駁捕蔔哺補埠不布步簿部怖擦猜裁材才財睬踩采彩菜蔡餐參蠶殘慚慘燦蒼艙倉滄藏操糙槽曹草廁策側冊測層蹭插叉茬茶查碴搽察岔差詫拆柴豺攙摻蟬饞讒纏鏟産闡顫昌猖場嘗常長償腸廠敞暢唱倡超抄鈔朝嘲潮巢吵炒車扯撤掣徹澈郴臣辰塵晨忱沈陳趁襯撐稱城橙成呈乘程懲澄誠承逞騁秤吃癡持匙池遲弛馳恥齒侈尺赤翅斥熾充沖蟲崇寵抽酬疇躊稠愁籌仇綢瞅醜臭初出櫥廚躇鋤雛滁除楚礎儲矗搐觸處揣川穿椽傳船喘串瘡窗幢床闖創吹炊捶錘垂春椿醇唇淳純蠢戳綽疵茨磁雌辭慈瓷詞此刺賜次聰蔥囪匆從叢湊粗醋簇促躥篡竄摧崔催脆瘁粹淬翠村存寸磋撮搓措挫錯搭達答瘩打大呆歹傣戴帶殆代貸袋待逮怠耽擔丹單鄲撣膽旦氮但憚淡誕彈蛋當擋黨蕩檔刀搗蹈倒島禱導到稻悼道盜德得的蹬燈登等瞪凳鄧堤低滴迪敵笛狄滌翟嫡抵底地蒂第帝弟遞締顛掂滇碘點典靛墊電佃甸店惦奠澱殿碉叼雕凋***掉吊釣調跌爹碟蝶叠諜疊丁盯叮釘頂鼎錠定訂丟東冬董懂動棟侗恫凍洞兜抖鬥陡豆逗痘都督毒犢獨讀堵睹賭杜鍍肚度渡妒端短鍛段斷緞堆兌隊對墩噸蹲敦頓囤鈍盾遁掇哆多奪垛躲朵跺舵剁惰墮蛾峨鵝俄額訛娥惡厄扼遏鄂餓恩而兒耳爾餌洱二貳發罰筏伐乏閥法琺藩帆番翻樊礬釩繁凡煩反返範販犯飯泛坊芳方肪房防妨仿訪紡放菲非啡飛肥匪誹吠肺廢沸費芬酚吩氛分紛墳焚汾粉奮份忿憤糞豐封楓蜂峰鋒風瘋烽逢馮縫諷奉鳳佛否夫敷膚孵扶拂輻幅氟符伏俘服浮涪福袱弗甫撫輔俯釜斧脯腑府腐赴副覆賦複傅付阜父腹負富訃附婦縛咐噶嘎該改概鈣蓋溉幹甘杆柑竿肝趕感稈敢贛岡剛鋼缸肛綱崗港杠篙臯高膏羔糕搞鎬稿告哥歌擱戈鴿胳疙割革葛格蛤閣隔鉻個各給根跟耕更庚羹埂耿梗工攻功恭龔供躬公宮弓鞏汞拱貢共鈎勾溝苟狗垢構購夠辜菇咕箍估沽孤姑鼓古蠱骨谷股故顧固雇刮瓜剮寡挂褂乖拐怪棺關官冠觀管館罐慣灌貫光廣逛瑰規圭矽歸龜閨軌鬼詭癸桂櫃跪貴劊輥滾棍鍋郭國果裹過哈骸孩海氦亥害駭酣憨邯韓含涵寒函喊罕翰撼捍旱憾悍焊汗漢夯杭航壕嚎豪毫郝好耗號浩呵喝荷菏核禾和何合盒貉閡河涸赫褐鶴賀嘿黑痕很狠恨哼亨橫衡恒轟哄烘虹鴻洪宏弘紅喉侯猴吼厚候後呼乎忽瑚壺葫胡蝴狐糊湖弧虎唬護互滬戶花嘩華猾滑畫劃化話槐徊懷淮壞歡環桓還緩換患喚瘓豢煥渙宦幻荒慌黃磺蝗簧皇凰惶煌晃幌恍謊灰揮輝徽恢蛔回毀悔慧卉惠晦賄穢會燴彙諱誨繪葷昏婚魂渾混豁活夥火獲或惑霍貨禍擊圾基機畸稽積箕肌饑迹激譏雞姬績緝吉極棘輯籍集及急疾汲即嫉級擠幾脊己薊技冀季伎祭劑悸濟寄寂計記既忌際妓繼紀嘉枷夾佳家加莢頰賈甲鉀假稼價架駕嫁殲監堅尖箋間煎兼肩艱奸緘繭檢柬堿鹼揀撿簡儉剪減薦檻鑒踐賤見鍵箭件健艦劍餞漸濺澗建僵姜將漿江疆蔣槳獎講匠醬降蕉椒礁焦膠交郊澆驕嬌嚼攪鉸矯僥腳狡角餃繳絞剿教酵轎較叫窖揭接皆稭街階截劫節莖睛晶鯨京驚精粳經井警景頸靜境敬鏡徑痙靖竟競淨炯窘揪究糾玖韭久灸九酒廄救舊臼舅咎就疚鞠拘狙疽居駒菊局咀矩舉沮聚拒據巨具距踞鋸俱句懼炬劇捐鵑娟倦眷卷絹撅攫抉掘倔爵桔傑捷睫竭潔結解姐戒藉芥界借介疥誡屆巾筋斤金今津襟緊錦僅謹進靳晉禁近燼浸盡勁荊兢覺決訣絕均菌鈞軍君峻俊竣浚郡駿喀咖卡咯開揩楷凱慨刊堪勘坎砍看康慷糠扛抗亢炕考拷烤靠坷苛柯棵磕顆科殼咳可渴克刻客課肯啃墾懇坑吭空恐孔控摳口扣寇枯哭窟苦酷庫褲誇垮挎跨胯塊筷儈快寬款匡筐狂框礦眶曠況虧盔巋窺葵奎魁傀饋愧潰坤昆捆困括擴廓闊垃拉喇蠟臘辣啦萊來賴藍婪欄攔籃闌蘭瀾讕攬覽懶纜爛濫琅榔狼廊郎朗浪撈勞牢老佬姥酪烙澇勒樂雷鐳蕾磊累儡壘擂肋類淚棱楞冷厘梨犁黎籬狸離漓理李裏鯉禮莉荔吏栗麗厲勵礫曆利傈例俐痢立粒瀝隸力璃哩倆聯蓮連鐮廉憐漣簾斂臉鏈戀煉練糧涼梁粱良兩輛量晾亮諒撩聊僚療燎寥遼潦了撂鐐廖料列裂烈劣獵琳林磷霖臨鄰鱗淋凜賃吝拎玲菱零齡鈴伶羚淩靈陵嶺領另令溜琉榴硫餾留劉瘤流柳六龍聾嚨籠窿隆壟攏隴樓婁摟簍漏陋蘆盧顱廬爐擄鹵虜魯麓碌露路賂鹿潞祿錄陸戮驢呂鋁侶旅履屢縷慮氯律率濾綠巒攣孿灤卵亂掠略掄輪倫侖淪綸論蘿螺羅邏鑼籮騾裸落洛駱絡媽麻瑪碼螞馬罵嘛嗎埋買麥賣邁脈瞞饅蠻滿蔓曼慢漫謾芒茫盲氓忙莽貓茅錨毛矛鉚卯茂冒帽貌貿麽玫枚梅酶黴煤沒眉媒鎂每美昧寐妹媚門悶們萌蒙檬盟錳猛夢孟眯醚靡糜迷謎彌米秘覓泌蜜密冪棉眠綿冕免勉娩緬面苗描瞄藐秒渺廟妙蔑滅民抿皿敏憫閩明螟鳴銘名命謬摸摹蘑模膜磨摩魔抹末莫墨默沫漠寞陌謀牟某拇牡畝姆母墓暮幕募慕木目睦牧穆拿哪呐鈉那娜納氖乃奶耐奈南男難囊撓腦惱鬧淖呢餒內嫩能妮霓倪泥尼擬你匿膩逆溺蔫拈年碾攆撚念娘釀鳥尿捏聶孽齧鑷鎳涅您檸獰凝甯擰濘牛扭鈕紐膿濃農弄奴努怒女暖虐瘧挪懦糯諾哦歐鷗毆藕嘔偶漚啪趴爬帕怕琶拍排牌徘湃派攀潘盤磐盼畔判叛乓龐旁耪胖抛咆刨炮袍跑泡呸胚培裴賠陪配佩沛噴盆砰抨烹澎彭蓬棚硼篷膨朋鵬捧碰坯砒霹批披劈琵毗啤脾疲皮匹痞僻屁譬篇偏片騙飄漂瓢票撇瞥拼頻貧品聘乒坪蘋萍平憑瓶評屏坡潑頗婆破魄迫粕剖撲鋪仆莆葡菩蒲埔樸圃普浦譜曝瀑期欺棲戚妻七淒漆柒沏其棋奇歧畦崎臍齊旗祈祁騎起豈乞企啓契砌器氣迄棄汽泣訖掐洽牽扡釺鉛千遷簽仟謙乾黔錢鉗前潛遣淺譴塹嵌欠歉槍嗆腔羌牆薔強搶橇鍬敲悄橋瞧喬僑巧鞘撬翹峭俏竅切茄且怯竊欽侵親秦琴勤芹擒禽寢沁青輕氫傾卿清擎晴氰情頃請慶瓊窮秋丘邱球求囚酋泅趨區蛆曲軀屈驅渠取娶齲趣去圈顴權醛泉全痊拳犬券勸缺炔瘸卻鵲榷確雀裙群然燃冉染瓤壤攘嚷讓饒擾繞惹熱壬仁人忍韌任認刃妊紉扔仍日戎茸蓉榮融熔溶容絨冗揉柔肉茹蠕儒孺如辱乳汝入褥軟阮蕊瑞銳閏潤若弱撒灑薩腮鰓塞賽三三傘散桑嗓喪搔騷掃嫂瑟色澀森僧莎砂殺刹沙紗傻啥煞篩曬珊苫杉山刪煽衫閃陝擅贍膳善汕扇繕墒傷商賞晌上尚裳梢捎稍燒芍勺韶少哨邵紹奢賒蛇舌舍赦攝射懾涉社設砷申呻伸身深娠紳神沈審嬸甚腎慎滲聲生甥牲升繩省盛剩勝聖師失獅施濕詩屍虱十石拾時什食蝕實識史矢使屎駛始式示士世柿事拭誓逝勢是嗜噬適仕侍釋飾氏市恃室視試收手首守壽授售受瘦獸蔬樞梳殊抒輸叔舒淑疏書贖孰熟薯暑曙署蜀黍鼠屬術述樹束戍豎墅庶數漱恕刷耍摔衰甩帥栓拴霜雙爽誰水睡稅吮瞬順舜說碩朔爍斯撕嘶思私司絲死肆寺嗣四伺似飼巳松聳慫頌送宋訟誦搜艘擻嗽蘇酥俗素速粟僳塑溯宿訴肅酸蒜算雖隋隨綏髓碎歲穗遂隧祟孫損筍蓑梭唆縮瑣索鎖所塌他它她塔獺撻蹋踏胎苔擡台泰酞太態汰坍攤貪癱灘壇檀痰潭譚談坦毯袒碳探歎炭湯塘搪堂棠膛唐糖倘躺淌趟燙掏濤滔縧萄桃逃淘陶討套特藤騰疼謄梯剔踢銻提題蹄啼體替嚏惕涕剃屜天添填田甜恬舔腆挑條迢眺跳貼鐵帖廳聽烴汀廷停亭庭挺艇通桐酮瞳同銅彤童桶捅筒統痛偷投頭透凸禿突圖徒途塗屠土吐兔湍團推頹腿蛻褪退吞屯臀拖托脫鴕陀馱駝橢妥拓唾挖哇蛙窪娃瓦襪歪外豌彎灣玩頑丸烷完碗挽晚皖惋宛婉萬腕汪王亡枉網往旺望忘妄威巍微危韋違桅圍唯惟爲濰維葦萎委偉僞尾緯未蔚味畏胃喂魏位渭謂尉慰衛瘟溫蚊文聞紋吻穩紊問嗡翁甕撾蝸渦窩我斡臥握沃巫嗚鎢烏汙誣屋無蕪梧吾吳毋武五捂午舞伍侮塢戊霧晤物勿務悟誤昔熙析西硒矽晰嘻吸錫犧稀息希悉膝夕惜熄烯溪汐犀檄襲席習媳喜銑洗系隙戲細瞎蝦匣霞轄暇峽俠狹下廈夏嚇掀鍁先仙鮮纖鹹賢銜舷閑涎弦嫌顯險現獻縣腺餡羨憲陷限線相廂鑲香箱襄湘鄉翔祥詳想響享項巷橡像向象蕭硝霄削哮囂銷消宵淆曉小孝校肖嘯笑效楔些歇蠍鞋協挾攜邪斜脅諧寫械卸蟹懈泄瀉謝屑薪芯鋅欣辛新忻心信釁星腥猩惺興刑型形邢行醒幸杏性姓兄凶胸匈洶雄熊休修羞朽嗅鏽秀袖繡墟戌需虛噓須徐許蓄酗敘旭序畜恤絮婿緒續軒喧宣懸旋玄選癬眩絢靴薛學穴雪血勳熏循旬詢尋馴巡殉汛訓訊遜迅壓押鴉鴨呀丫芽牙蚜崖衙涯雅啞亞訝焉咽閹煙淹鹽嚴研蜒岩延言顔閻炎沿奄掩眼衍演豔堰燕厭硯雁唁彥焰宴諺驗殃央鴦秧楊揚佯瘍羊洋陽氧仰癢養樣漾邀腰妖瑤搖堯遙窯謠姚咬舀藥要耀椰噎耶爺野冶也頁掖業葉曳腋夜液一壹醫揖銥依伊衣頤夷遺移儀胰疑沂宜姨彜椅蟻倚已乙矣以藝抑易邑屹億役臆逸肄疫亦裔意毅憶義益溢詣議誼譯異翼翌繹茵蔭因殷音陰姻吟銀淫寅飲尹引隱印英櫻嬰鷹應纓瑩螢營熒蠅迎贏盈影穎硬映喲擁傭臃癰庸雍踴蛹詠泳湧永恿勇用幽優悠憂尤由郵鈾猶油遊酉有友右佑釉誘又幼迂淤于盂榆虞愚輿余俞逾魚愉渝漁隅予娛雨與嶼禹宇語羽玉域芋郁籲遇喻峪禦愈欲獄育譽浴寓裕預豫馭鴛淵冤元垣袁原援轅園員圓猿源緣遠苑願怨院曰約越躍鑰嶽粵月悅閱耘雲鄖勻隕允運蘊醞暈韻孕匝砸雜栽哉災宰載再在咱攢暫贊贓髒葬遭糟鑿藻棗早澡蚤躁噪造皂竈燥責擇則澤賊怎增憎曾贈紮喳渣劄軋鍘閘眨柵榨咋乍炸詐摘齋宅窄債寨瞻氈詹粘沾盞斬輾嶄展蘸棧占戰站湛綻樟章彰漳張掌漲杖丈帳賬仗脹瘴障招昭找沼趙照罩兆肇召遮折哲蟄轍者鍺蔗這浙珍斟真甄砧臻貞針偵枕疹診震振鎮陣蒸掙睜征猙爭怔整拯正政幀症鄭證芝枝支吱蜘知肢脂汁之織職直植殖執值侄址指止趾只旨紙志摯擲至致置幟峙制智秩稚質炙痔滯治窒中盅忠鍾衷終種腫重仲衆舟周州洲謅粥軸肘帚咒皺宙晝驟珠株蛛朱豬諸誅逐竹燭煮拄矚囑主著柱助蛀貯鑄築住注祝駐抓爪拽專磚轉撰賺篆樁莊裝妝撞壯狀椎錐追贅墜綴諄准捉拙卓桌琢茁酌啄著灼濁茲咨資姿滋淄孜紫仔籽滓子自漬字鬃棕蹤宗綜總縱鄒走奏揍租足卒族祖詛阻組鑽纂嘴醉最罪尊遵昨左佐柞做作坐座' declare @j nvarchar(2000),@f nvarchar(2000) select @j='皑蔼碍爱翱袄奥坝罢摆败颁办绊帮绑镑谤剥饱宝报鲍辈贝钡狈备惫绷笔毕毙币闭边编贬变辩辫标鳖别瘪濒滨宾摈饼并拨钵铂驳卜补财参蚕残惭惨灿苍舱仓沧厕侧册测层诧搀掺蝉馋谗缠铲产阐颤场尝长偿肠厂畅钞车彻尘沉陈衬撑称惩诚骋痴迟驰耻齿炽冲虫宠畴踌筹绸丑橱厨锄雏础储触处传疮闯创锤纯绰辞词赐聪葱囱从丛凑蹿窜错达带贷担单郸掸胆惮诞弹当挡党荡档捣岛祷导盗灯邓敌涤递缔颠点垫电淀钓调迭谍叠钉顶锭订丢东动栋冻斗犊独读赌镀锻断缎兑队对吨顿钝夺堕鹅额讹恶饿儿尔饵贰发罚阀珐矾钒烦范贩饭访纺飞诽废费纷坟奋愤粪丰枫锋风疯冯缝讽凤肤辐抚辅赋复负讣妇缚该钙盖干赶秆赣冈刚钢纲岗皋镐搁鸽阁铬个给龚宫巩贡钩沟构购够蛊顾剐关观馆惯贯广规硅归龟闺轨诡柜贵刽辊滚锅国过骇韩汉号阂鹤贺横轰鸿红后壶护沪户哗华画划话怀坏欢环还缓换唤痪焕涣黄谎挥辉毁贿秽会烩汇讳诲绘荤浑伙获货祸击机积饥讥鸡绩缉极辑级挤几蓟剂济计记际继纪夹荚颊贾钾价驾歼监坚笺间艰缄茧检碱硷拣捡简俭减荐槛鉴践贱见键舰剑饯渐溅涧将浆蒋桨奖讲酱胶浇骄娇搅铰矫侥脚饺缴绞轿较秸阶节茎鲸惊经颈静镜径痉竞净纠厩旧驹举据锯惧剧鹃绢杰洁结诫届紧锦仅谨进晋烬尽劲荆觉决诀绝钧军骏开凯颗壳课垦恳抠库裤夸块侩宽矿旷况亏岿窥馈溃扩阔蜡腊莱来赖蓝栏拦篮阑兰澜谰揽览懒缆烂滥捞劳涝乐镭垒类泪篱离里鲤礼丽厉励砾历沥隶俩联莲连镰怜涟帘敛脸链恋炼练粮凉两辆谅疗辽镣猎临邻鳞凛赁龄铃凌灵岭领馏刘龙聋咙笼垄拢陇楼娄搂篓芦卢颅庐炉掳卤虏鲁赂禄录陆驴吕铝侣屡缕虑滤绿峦挛孪滦乱抡轮伦仑沦纶论萝罗逻锣箩骡骆络妈玛码蚂马骂吗买麦卖迈脉瞒馒蛮满谩猫锚铆贸么霉没镁门闷们锰梦谜弥觅幂绵缅庙灭悯闽鸣铭谬谋亩钠纳难挠脑恼闹馁内拟腻撵捻酿鸟聂啮镊镍柠狞宁拧泞钮纽脓浓农疟诺欧鸥殴呕沤盘庞赔喷鹏骗飘频贫苹凭评泼颇扑铺朴谱栖凄脐齐骑岂启气弃讫牵扦钎铅迁签谦钱钳潜浅谴堑枪呛墙蔷强抢锹桥乔侨翘窍窃钦亲寝轻氢倾顷请庆琼穷趋区躯驱龋颧权劝却鹊确让饶扰绕热韧认纫荣绒软锐闰润洒萨鳃赛叁伞丧骚扫涩杀纱筛晒删闪陕赡缮伤赏烧绍赊摄慑设绅审婶肾渗声绳胜圣师狮湿诗尸时蚀实识驶势适释饰视试寿兽枢输书赎属术树竖数帅双谁税顺说硕烁丝饲耸怂颂讼诵擞苏诉肃虽随绥岁孙损笋缩琐锁獭挞抬态摊贪瘫滩坛谭谈叹汤烫涛绦讨腾誊锑题体屉条贴铁厅听烃铜统头秃图涂团颓蜕脱鸵驮驼椭洼袜弯湾顽万网韦违围为潍维苇伟伪纬谓卫温闻纹稳问瓮挝蜗涡窝卧呜钨乌污诬无芜吴坞雾务误锡牺袭习铣戏细虾辖峡侠狭厦吓锨鲜纤咸贤衔闲显险现献县馅羡宪线厢镶乡详响项萧嚣销晓啸蝎协挟携胁谐写泻谢锌衅兴汹锈绣虚嘘须许叙绪续轩悬选癣绚学勋询寻驯训讯逊压鸦鸭哑亚讶阉烟盐严颜阎艳厌砚彦谚验鸯杨扬疡阳痒养样瑶摇尧遥窑谣药爷页业叶医铱颐遗仪彝蚁艺亿忆义诣议谊译异绎荫阴银饮隐樱婴鹰应缨莹萤营荧蝇赢颖哟拥佣痈踊咏涌优忧邮铀犹游诱舆鱼渔娱与屿语吁御狱誉预驭鸳渊辕园员圆缘远愿约跃钥岳粤悦阅云郧匀陨运蕴酝晕韵杂灾载攒暂赞赃脏凿枣灶责择则泽贼赠扎札轧铡闸栅诈斋债毡盏斩辗崭栈战绽张涨帐账胀赵蛰辙锗这贞针侦诊镇阵挣睁狰争帧郑证织职执纸挚掷帜质滞钟终种肿众诌轴皱昼骤猪诸诛烛瞩嘱贮铸筑驻专砖转赚桩庄装妆壮状锥赘坠缀谆着浊兹资渍踪综总纵邹诅组钻' ,@f='皚藹礙愛翺襖奧壩罷擺敗頒辦絆幫綁鎊謗剝飽寶報鮑輩貝鋇狽備憊繃筆畢斃幣閉邊編貶變辯辮標鼈別癟瀕濱賓擯餅並撥缽鉑駁蔔補財參蠶殘慚慘燦蒼艙倉滄廁側冊測層詫攙摻蟬饞讒纏鏟産闡顫場嘗長償腸廠暢鈔車徹塵沈陳襯撐稱懲誠騁癡遲馳恥齒熾沖蟲寵疇躊籌綢醜櫥廚鋤雛礎儲觸處傳瘡闖創錘純綽辭詞賜聰蔥囪從叢湊躥竄錯達帶貸擔單鄲撣膽憚誕彈當擋黨蕩檔搗島禱導盜燈鄧敵滌遞締顛點墊電澱釣調叠諜疊釘頂錠訂丟東動棟凍鬥犢獨讀賭鍍鍛斷緞兌隊對噸頓鈍奪墮鵝額訛惡餓兒爾餌貳發罰閥琺礬釩煩範販飯訪紡飛誹廢費紛墳奮憤糞豐楓鋒風瘋馮縫諷鳳膚輻撫輔賦複負訃婦縛該鈣蓋幹趕稈贛岡剛鋼綱崗臯鎬擱鴿閣鉻個給龔宮鞏貢鈎溝構購夠蠱顧剮關觀館慣貫廣規矽歸龜閨軌詭櫃貴劊輥滾鍋國過駭韓漢號閡鶴賀橫轟鴻紅後壺護滬戶嘩華畫劃話懷壞歡環還緩換喚瘓煥渙黃謊揮輝毀賄穢會燴彙諱誨繪葷渾夥獲貨禍擊機積饑譏雞績緝極輯級擠幾薊劑濟計記際繼紀夾莢頰賈鉀價駕殲監堅箋間艱緘繭檢堿鹼揀撿簡儉減薦檻鑒踐賤見鍵艦劍餞漸濺澗將漿蔣槳獎講醬膠澆驕嬌攪鉸矯僥腳餃繳絞轎較稭階節莖鯨驚經頸靜鏡徑痙競淨糾廄舊駒舉據鋸懼劇鵑絹傑潔結誡屆緊錦僅謹進晉燼盡勁荊覺決訣絕鈞軍駿開凱顆殼課墾懇摳庫褲誇塊儈寬礦曠況虧巋窺饋潰擴闊蠟臘萊來賴藍欄攔籃闌蘭瀾讕攬覽懶纜爛濫撈勞澇樂鐳壘類淚籬離裏鯉禮麗厲勵礫曆瀝隸倆聯蓮連鐮憐漣簾斂臉鏈戀煉練糧涼兩輛諒療遼鐐獵臨鄰鱗凜賃齡鈴淩靈嶺領餾劉龍聾嚨籠壟攏隴樓婁摟簍蘆盧顱廬爐擄鹵虜魯賂祿錄陸驢呂鋁侶屢縷慮濾綠巒攣孿灤亂掄輪倫侖淪綸論蘿羅邏鑼籮騾駱絡媽瑪碼螞馬罵嗎買麥賣邁脈瞞饅蠻滿謾貓錨鉚貿麽黴沒鎂門悶們錳夢謎彌覓冪綿緬廟滅憫閩鳴銘謬謀畝鈉納難撓腦惱鬧餒內擬膩攆撚釀鳥聶齧鑷鎳檸獰甯擰濘鈕紐膿濃農瘧諾歐鷗毆嘔漚盤龐賠噴鵬騙飄頻貧蘋憑評潑頗撲鋪樸譜棲淒臍齊騎豈啓氣棄訖牽扡釺鉛遷簽謙錢鉗潛淺譴塹槍嗆牆薔強搶鍬橋喬僑翹竅竊欽親寢輕氫傾頃請慶瓊窮趨區軀驅齲顴權勸卻鵲確讓饒擾繞熱韌認紉榮絨軟銳閏潤灑薩鰓賽三傘喪騷掃澀殺紗篩曬刪閃陝贍繕傷賞燒紹賒攝懾設紳審嬸腎滲聲繩勝聖師獅濕詩屍時蝕實識駛勢適釋飾視試壽獸樞輸書贖屬術樹豎數帥雙誰稅順說碩爍絲飼聳慫頌訟誦擻蘇訴肅雖隨綏歲孫損筍縮瑣鎖獺撻擡態攤貪癱灘壇譚談歎湯燙濤縧討騰謄銻題體屜條貼鐵廳聽烴銅統頭禿圖塗團頹蛻脫鴕馱駝橢窪襪彎灣頑萬網韋違圍爲濰維葦偉僞緯謂衛溫聞紋穩問甕撾蝸渦窩臥嗚鎢烏汙誣無蕪吳塢霧務誤錫犧襲習銑戲細蝦轄峽俠狹廈嚇鍁鮮纖鹹賢銜閑顯險現獻縣餡羨憲線廂鑲鄉詳響項蕭囂銷曉嘯蠍協挾攜脅諧寫瀉謝鋅釁興洶鏽繡虛噓須許敘緒續軒懸選癬絢學勳詢尋馴訓訊遜壓鴉鴨啞亞訝閹煙鹽嚴顔閻豔厭硯彥諺驗鴦楊揚瘍陽癢養樣瑤搖堯遙窯謠藥爺頁業葉醫銥頤遺儀彜蟻藝億憶義詣議誼譯異繹蔭陰銀飲隱櫻嬰鷹應纓瑩螢營熒蠅贏穎喲擁傭癰踴詠湧優憂郵鈾猶遊誘輿魚漁娛與嶼語籲禦獄譽預馭鴛淵轅園員圓緣遠願約躍鑰嶽粵悅閱雲鄖勻隕運蘊醞暈韻雜災載攢暫贊贓髒鑿棗竈責擇則澤賊贈紮劄軋鍘閘柵詐齋債氈盞斬輾嶄棧戰綻張漲帳賬脹趙蟄轍鍺這貞針偵診鎮陣掙睜猙爭幀鄭證織職執紙摯擲幟質滯鍾終種腫衆謅軸皺晝驟豬諸誅燭矚囑貯鑄築駐專磚轉賺樁莊裝妝壯狀錐贅墜綴諄著濁茲資漬蹤綜總縱鄒詛組鑽' --生成码表 create table codetable(gb nchar(1),big nchar(1)) select top 1298 id=identity(int,1,1) into #t from syscolumns a,syscolumns b insert codetable select substring(@j,id,1),substring(@f,id,1) from #t where id<=len(@j) drop table #t go /*--简繁转换函数 利用编码对照表,实现简体-->繁体,繁体-->简体的转换 注意,转换函数使用的是unicode编码 --邹建.07--*/ IF NOT OBJECT_ID('[dbo].[f_GB2BIG]') IS NULL DROP FUNCTION [dbo].[f_GB2BIG] GO CREATE FUNCTION f_GB2BIG( @str nvarchar(4000), --要转换的字符串 @toBIG bit --转换标志,为,表示GB-->BIG,否则是BIG-->GB )RETURNS nvarchar(4000) AS BEGIN IF @toBIG=1 SELECT @str=REPLACE(@str,gb,big) FROM codetable WHERE CHARINDEX(gb,@str)>0 ELSE SELECT @str=replace(@str,big,gb) FROM codetable WHERE charindex(big,@str)>0 RETURN(@str) END GO --测试示例 --转换为繁体 select dbo.f_GB2BIG('我是中国人',1) --转换为简体 select dbo.f_GB2BIG('我是中國人',0) --运行结果 /* 我是中國人 我是中国人 */ /* 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/20/5506045.aspx */
31、自定义函数实现位操作
--原帖地址：http://blog.csdn.net/Haiwer/archive/2007/07/21/1701476.aspx --问题： --比如两个字符串分别是'000111011001'和'010011010011' --需要求他们的与，结果是000011010001 /************************************************/ /* 字符串与操作 */ /* 版本: 1.0 */ /* 作者: Haiwer */ /* 版权所有 */ /* 调用事例:select dbo.fn_and('000111011001','010011010011')*/ /* 2007.07.21整理 */ /************************************************/ go --创建函数 CREATE function [dbo].[fn_And]( @A1 varchar(300), @A2 varchar(300) ) returns varchar(300) as begin declare @r varchar(300) set @r='' while len(@A1) >0 begin set @r=@r+cast(cast(left(@A1,1) as tinyint) & cast(left(@A2,1) as tinyint) as varchar) set @A1=stuff(@A1,1,1,'') set @A2=stuff(@A2,1,1,'') end return @r end --测试示例 select dbo.fn_and('000111011001','010011010011') --运行结果 /* 000011010001 */ --问题：有表tab数据如下 /* m_test'@table ID Val A 3 A 2 A 1 B 4 B 2 C 1 C 2 C 8 C 16 B 32' */ --求每个id的聚合或，要求的结果如下 /* ID Val A 3 --3 or 2 or 1 =3 B 38 --4 or 2 or 32=38 C 27 -- 1 or 2 or 8 or 16=27 */ go --创建测试数据 create table tab(ID varchar(1),Val int) insert into tab select 'A',3 union all select 'A',2 union all select 'A',1 union all select 'B',4 union all select 'B',2 union all select 'C',1 union all select 'C',2 union all select 'C',8 union all select 'C',16 union all select 'B',32 --用函数实现 /************************************************/ /* 聚合或操作函数 */ /* 版本: 1.0 */ /* 作者: Haiwer */ /* 版权所有 */ /* 调用事例: */ /* select id,[dbo].[fn_聚合或](id) as 聚合或from tab group by id */ /* 2007.07.21整理 */ /************************************************/ go --创建函数 create function [dbo].[fn_聚合或] ( @id varchar(10) ) returns int as begin declare @r int set @r=0 select @r=@r | val from tab where id=@id return @r end go --测试示例 select id,[dbo].[fn_聚合或](id) as 聚合或from tab group by id --运行结果 /* id 聚合或 ---- ----------- A 3 B 38 C 27 */ --本文来自CSDN博客 --转载请标明出处： --http://blog.csdn.net/Haiwer/archive/2007/07/21/1701476.aspx
32、求某段时间内星期几的天数
--原帖地址：http://blog.csdn.net/DengXingJie/archive/2011/02/25/6208762.aspx /* ********************************************** Program ID: FUN_GetDaysOfWeek Purpose : 求某段时间内某一星期日期的天数 Author : Jesse Date : 2011.02.19 ********************************************** */ go Create Function dbo.FUN_GetDaysOfWeek( @DateS smalldatetime,--开始时间 @DateE smalldatetime,--结束 @WeekValue int) --星期的某一天：-周日、-周一...7-周六 Returns nvarchar(100) As Begin declare @sResult nvarchar(100), --结果描述 @nResult int, --结果天数 @nWeekValue int, --起始日期是星期几(1-周日、-周一...7-周六) @nDays int, --时间段内的天数 @nBeforeDays int, --起始日期到第一次相符日期的天数 @sWeekName varchar(20), --所求日期的星期名称 @tmpDate smalldatetime set @WeekValue=@WeekValue % 7 if @WeekValue=0 set @WeekValue=7 --如果起始日期大于终止日期，则对换 if @DateS>@DateE select @tmpDate=@DateE,@DateE=@DateS,@DateS=@tmpDate --取得起始日期的星期数，与设置无关(1-周日、-周一...7-周六) select @nWeekValue=(Datepart(dw,@DateS)+(@@Datefirst %7))%7 if @nWeekValue=0 set @nWeekValue=7 set @sWeekName=DateName(dw,DateAdd(d,@WeekValue-@nWeekValue,@DateS)) if @WeekValue>=@nWeekValue set @nBeforeDays=@WeekValue-@nWeekValue else set @nBeforeDays=7-abs(@WeekValue-@nWeekValue) --取得时间段内的天数 select @nDays=Datediff(d,@DateS,@DateE)+1 if @nDays<@nBeforeDays select @nResult=0 else select @nResult=ceiling((@nDays-@nBeforeDays)/7.0) set @sResult=N'日期区间：'+ convert(varchar(10),@DateS,120)+ N' 到' +convert(varchar(10),@DateE,120) +' 之间' +@sWeekName +N' 共有'+cast(@nResult as varchar(100))+N' 天' Return @sResult end go --测试示例 select dbo.FUN_GetDaysOfWeek('2011-03-01','2011-05-31',1) --运行结果 /* 日期区间：2011-03-01 到2011-05-31 之间Sunday 共有13 天 */
33、根据进舍位或四舍五入来求值
--原帖地址： --http://blog.csdn.net/DengXingJie/archive/2011/02/25/6208613.aspx /* *************************************** Program ID: FUN_GetValueByRoundMode Purpose : 根据进舍位或四舍五入来求值 Author : Jesse Date : 2011.02.14 *************************************** */ go --创建函数 Create function dbo.FUN_GetValueByRoundMode ( @Value decimal(18,6), --需进行运算的值 @Bit int, --小数位数 @RoundMode char(1)) --求值方式：-四舍五入、-舍去、-进位 Returns decimal(18,6) As begin declare @Result decimal(18,6) set @Value=isnull(@Value,0) if @RoundMode='1' --舍去 Select @Result=Round(@Value,@Bit,1) else if @RoundMode='2' --进位 Begin if @Value>Round(@Value,@Bit,1) --确保在有尾数的情况下才进位 Select @Result=Round(@Value,@Bit,1)+1.0/Power(10,@Bit) else Select @Result=Round(@Value,@Bit,1) end else Select @Result=Round(@Value,@Bit) Return Isnull(@Result,0) end --测试示例 select dbo.FUN_GetValueByRoundMode(9.82458,3,'0') select dbo.FUN_GetValueByRoundMode(9.82458,3,'1') select dbo.FUN_GetValueByRoundMode(9.82458,3,'2') --运行结果 /* 9.825000 9.824000 9.825000 */
34、字符串转成16进制函数
/**************************** 字符串转成16进制 作者：不得闲 QQ: 75492895 Email: appleak46@yahoo.com.cn ****************************/ --创建函数(suiyunonghen(不得闲)) Create Function VarCharToHex(@Str Varchar(400)) returns varchar(800) as begin declare @i int,@Asi int,@ModS int,@res varchar(800),@Len int,@Cres varchar(4),@tempstr varbinary(400) select @i = 1,@res='',@len=datalength(@str),@tempStr = Convert(varbinary,@str) while @i<=@len begin Select @Asi = substring(@tempstr,1,1),@Cres='' while @Asi <> 0 begin select @Mods = @Asi %16, @Cres=Case when (@Mods > 9) then Char(Ascii('A')+@Mods-10)+@Cres else Cast(@Mods as varchar(4)) + @Cres end, @Asi = @Asi/16 end Select @res = @res + @Cres,@tempStr = substring(@tempStr,2,@len-1),@i = @i+1 end return @res end go --测试示例 select dbo.VarCharToHex('叶子') --运行结果 /* D2B6D7D3 */
35、去掉字段中多个带区号电话号码前面的区号
--原帖地址：http://blog.csdn.net/htl258/archive/2010/04/28/5540795.aspx --------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-28 23:22:15 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Blog : http://blog.csdn.net/htl258 -- Subject: 应用实例：去掉字段中多个带区号电话号码前面的区号 -------------------------------------------------------------------- --需求贴：http://topic.csdn.net/u/20100428/20/f2572998-099c-463a-a530-707a40606c9c.html?53227 --> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([name] NVARCHAR(10),[phone] NVARCHAR(40)) INSERT [tb] SELECT 'a',N'010-12345678/0571-86919111' UNION ALL SELECT 'b',N'020-23950423/0756-34972654/023-89732456' GO --SELECT * FROM [tb] -->SQL查询如下: --1.创建自定义函数 IF NOT OBJECT_ID('[f_getphone]') IS NULL DROP FUNCTION [f_getphone] GO CREATE FUNCTION f_getphone(@s varchar(200)) RETURNS varchar(200) AS BEGIN SET @s=@s+'/' DECLARE @r VARCHAR(200) WHILE CHARINDEX('/',@s)>0 SELECT @r=ISNULL(@r+'/','') +LEFT(STUFF(@s,1,CHARINDEX('-',@s),'') ,CHARINDEX('/',@s)-CHARINDEX('-',@s)-1) ,@s=STUFF(@s,1,CHARINDEX('/',@s),'') RETURN @r END GO --2.查询 SELECT [name],dbo.f_getphone(phone) 'phone' FROM TB /* name phone ---------- ------------------------------------ a 12345678/86919111 b 23950423/34972654/89732456 (2 行受影响) */ --本文来自CSDN博客 --转载请标明出处：--http://blog.csdn.net/htl258/archive/2010/04/28/5540795.aspx
36、SQL2000/2005字符串拆分为列表通用函数
-- 原帖地址：http://blog.csdn.net/htl258/archive/2010/04/28/5537235.aspx ------------------------------------------------------------------ -- Author : htl258(Tony) -- Date : 2010-04-28 02:00:28 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:38 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Blog : http://blog.csdn.net/htl258 -- Subject: SQL2000/2005字符串拆分为列表通用函数 ------------------------------------------------------------------- --SQL2000/2005字符串拆分为列表通用函数 IF OBJECT_ID('f_getstr') IS NOT NULL DROP FUNCTION f_getstr GO CREATE FUNCTION f_getstr( @s NVARCHAR(4000), --待分拆的字符串 @flag NVARCHAR(10)='' --数据分隔符 )RETURNS @r TABLE(col NVARCHAR(1000)) AS BEGIN IF ISNULL(@flag,'')='' AND LEN(ISNULL(@flag,'')+'a')=1 INSERT @r SELECT SUBSTRING(@s,number+1,1) FROM master..spt_values WHERE TYPE='p' and number<LEN(@s+'a')-1 ELSE INSERT @r SELECT SUBSTRING(@s,number,CHARINDEX(@flag,@s+@flag,number)-number) FROM master..spt_values WHERE TYPE='p' and number<=len(@s+'a') --AND SUBSTRING(@flag+@s,number,1)=@flag --用此条件或下面的条件均可 AND CHARINDEX(@flag,@flag+@s,number)=number RETURN END GO --本实例技巧，利用master库自带的spt_values表，取number字段作为连续的序号， --省去创建序号表，尽量做到通用，再加上字符串处理函数取得最终结果。 --1.每个字符拆分取出 SELECT * FROM dbo.f_getstr(N'一个世界一个家',NULL) SELECT * FROM dbo.f_getstr(N'一个世界一个家','') SELECT * FROM dbo.f_getstr(N'一个世界一个家',default) /* col ------- 一 个 世 界 一 个 家 (7 行受影响) */ --2.指定分隔符拆分取出 SELECT * FROM dbo.f_getstr(N'一个世界一个家',N' ') SELECT * FROM dbo.f_getstr(N'一个,世界,一个,家',N',') SELECT * FROM dbo.f_getstr(N'一个%世界%一个%家',N'%') SELECT * FROM dbo.f_getstr(N'一个中国世界中国一个中国家',N'中国') /* col --------- 一个 世界 一个 家 (4 行受影响) */ --3.SQL2005以上版本可以结合apply进行拆分列值 IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb (id INT,col VARCHAR(30)) INSERT INTO tb VALUES(1,'aa,bb') INSERT INTO tb VALUES(2,'aaa,bbb,ccc') GO SELECT id,b.col FROM tb CROSS APPLY f_getstr(col,',') b SELECT id,b.col FROM tb OUTER APPLY f_getstr(col,',') b /* id col ----------- ----------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响) */ --本文来自CSDN博客 --转载请标明出处： --http://blog.csdn.net/htl258/archive/2010/04/28/5537235.aspx
37、求字符串中汉字的个数
一、分解字符串法 首先创建这个函数： /*将字符串分解*/ create function [dbo].[SplitChar] ( @str_One Nvarchar(100) ) returns @Result table (col nvarchar(1)) AS BEGIN declare @number_One int select @number_One=1 while @number_One<=len(@str_One) begin insert @Result select substring(@str_One,@number_One,1) select @number_One=@number_One+1 end return END 然后创建如下函数 /*求字符串中汉字个数*/ Create function [dbo].[ChineseCountOne] ( @Str_One nvarchar(200) ) RETURNS int AS BEGIN declare @number_One int SELECT @number_One =count(*) from dbo.SplitChar(@Str_One) where ascii(col)>127 return @number_One END /*使用示例 select dbo.[ChineseCountOne] ('China中国Beijing北京Olympics奥林匹克') */ 二、字符字节求差法 首先创建这个函数： create function [dbo].[Chinesecount_Two] ( @Str_One varchar(200) ) RETURNS int AS BEGIN declare @number_One int set @number_One=(datalength(@Str_One)-len(@Str_One)) return @number_One END /*使用示例 DECLARE @sql_one varchar(200) SET @sql_one='China中国Beijing北京Olympics奥林匹克' DECLARE @sql_two nvarchar(200) SET @sql_two='China中国Beijing北京Olympics奥林匹克' select dbo.[Chinesecount_Two] (@sql_one) '个数one' , dbo.[Chinesecount_Two] (@sql_two) '个数two' --此例说明此方法不受数据类型限制 */
38、得到条形码的校验位函数
二、SQL实现：
go
-- =============================================
-- Author: <Maco Wang>
-- Create date: <2009-10-27 16:01>
-- Description: <计算条形码的校验码>
-- =============================================
create function [dbo].[Get_CheckCode]
(
@ActiveCode varchar(12)
)
returns varchar(13)
as
begin
declare @codelength int
set @codelength= len(@ActiveCode)
declare @curnum int;set @curnum=0
declare @temp1 int;set @temp1=0
declare @temp2 int;set @temp2=0
declare @locatnum int;set @locatnum=0
declare @code13 int
declare @i int;set @i=0
while(@i<@codelength)
begin
set @locatnum=@i+2;
set @curnum=floor(substring(@ActiveCode,@codelength-@i,1))
if(@locatnum%2=0)
set @temp1=@temp1+@curnum
else
set @temp2=@temp2+@curnum
set @i=@i+1
end
set @code13=10-(@temp1*3+@temp2)%10;
if(@code13=10)
set @code13=0
return @ActiveCode+cast(@code13 as varchar(1));
end
--测试示例
select dbo.[Get_CheckCode]('827123121212')
--运行结果
/*
8271231212124
*/
39、根据年月得到当月的天数
go --创建函数 create function [dbo].[get_days] ( @yearmonth varchar(6) --参数格式： ) returns int as begin return (datediff(d,@yearmonth+'01',dateadd(mm,1,@yearmonth+'01'))) end --测试示例 select dbo.[get_days]('201103') --运行结果 /* 31 */
40、将一个正整数分解为m个2的n次方的和
-- ============================================= -- Author: <maco_wang> -- Create date: <2011-01-21> -- Description: <将一个正整数分解为m个2的n次方的和> -- ============================================= go --创建函数 CREATE function GetSumSequence(@num INT) returns nvarchar(200) AS BEGIN DECLARE @numc INT SET @numc = @num DECLARE @numstr VARCHAR(50) SET @numstr = '' WHILE ( @num <> 0 ) BEGIN SET @numstr = @numstr + CONVERT(CHAR(1), @num % 2) SET @num = @num / 2 END --SELECT REVERSE(@numstr) DECLARE @i INT SET @i = LEN(@numstr) DECLARE @j VARCHAR(MAX) SET @j = '' WHILE ( @i > 0 ) BEGIN IF ( SUBSTRING(REVERSE(@numstr), LEN(@numstr) - @i + 1, 1) = '1' ) SELECT @j = @j + '+2^' + CAST (@i-1 AS VARCHAR(10)) SET @i = @i - 1 END return (CAST(@numc AS VARCHAR(100)) + '=' + STUFF(@j, 1, 1, '')) END go --测试示例 select dbo.GetSumSequence(12) select dbo.GetSumSequence(65) select dbo.GetSumSequence(892) select dbo.GetSumSequence(1919191) --运行结果 /* 12=2^3+2^2 65=2^6+2^0 892=2^9+2^8+2^6+2^5+2^4+2^3+2^2 1919191=2^20+2^19+2^18+2^16+2^14+2^11+2^7+2^6+2^4+2^2+2^1+2^0 */
41、SQL位移运算函数
-- ============================================= -- Author: <maco_wang> -- Create date: <2011-03-22> -- Description: <SQL位移运算函数> -- ============================================= go --创建函数 create function displacement(@n as bigint,@m as varchar(3)) returns int as begin declare @maco varchar(50);set @maco='' declare @i int declare @x int declare @s int while (@n<>0) begin set @maco=@maco+convert(char(1),@n%2) set @n=@n/2 end set @maco=reverse(@maco) set @maco=RIGHT('0000'+@maco,4) set @s=LEN(@maco) set @i=convert(int,RIGHT(@m,1)) set @x=1 if LEFT(@m,2)='<<' begin while(@x<=@i) begin select @maco=@maco+'0' select @x=@x+1 end select @maco=RIGHT(@maco,@s) end if LEFT(@m,2)='>>' begin while(@x<=@i) begin select @maco='0'+@maco select @x=@x+1 end select @maco=LEFT(@maco,@s) end set @i=LEN(@maco) set @s=0 while(@i>0) begin if SUBSTRING(@maco,LEN(@maco)-@i+1,1)='1' begin select @s=@s+POWER(2,convert(float,@i-1)) end select @i=@i-1 end return @s end --测试示例 select dbo.displacement(1,'<<3') select dbo.displacement(8,'>>1') select dbo.displacement(12,'>>3') --运行结果 /* 8 4 1 */
42、得到汉字笔画函数
--=============================================== --功能：汉字笔画函数 --说明：以单个汉字汉字为参数返回每一个汉字的笔画数 --作者: J9988 --*/ --=============================================== create function [dbo].[fun_getbh](@char nchar(2)) returns int as begin return( case when unicode(@char) between 19968 and 40869 then( select top 1 id from( select id=1,ch=N'亅' union all select 2,N'阝' union all select 3,N'马' union all select 4,N'风' union all select 5,N'龙' union all select 6,N'齐' union all select 7,N'龟' union all select 8,N'齿' union all select 9,N'鸩' union all select 10,N'龀' union all select 11,N'龛' union all select 12,N'龂' union all select 13,N'龆' union all select 14,N'龈' union all select 15,N'龊' union all select 16,N'龍' union all select 17,N'龠' union all select 18,N'龎' union all select 19,N'龐' union all select 20,N'龑' union all select 21,N'龡' union all select 22,N'龢' union all select 23,N'龝' union all select 24,N'齹' union all select 25,N'龣' union all select 26,N'龥' union all select 27,N'齈' union all select 28,N'龞' union all select 29,N'麷' union all select 30,N'鸞' union all select 31,N'麣' union all select 32,N'龖' union all select 33,N'龗' union all select 35,N'齾' union all select 36,N'齉' union all select 39,N'靐' union all select 64,N'龘' )a where ch>=@char collate Chinese_PRC_Stroke_CS_AS_KS_WS order by id ASC) else 0 end) end --测试示例 select dbo.fun_getbh('晓') --运行结果 /* 10 */
43、SQL数字转英文函数
--晴天兄(qianjin036a)的发帖地址： --http://topic.csdn.net/u/20080614/12/d26adea8-ac05-4b06-8b8a-f46a4b564e3b.html -- 数字转英文 -- ============================================= -- Author: qianjin036a -- Create date:06/14/2008 02:27:17 -- Description:Arabic numerals to English -- ============================================= go --创建函数 CREATE FUNCTION Digit2English ( @arabia decimal(38,17) ) RETURNS varchar(1000) AS BEGIN declare @atoe table(a int,e varchar(10)) insert into @atoe select 0,'zero' union all select 1,'one' union all select 2,'two' union all select 3,'three' union all select 4,'four' union all select 5,'five' union all select 6,'six' union all select 7,'seven' union all select 8,'eight' union all select 9,'nine' declare @integer bigint,@trillion int,@billion int,@million int,@thousand int,@hundred int,@english varchar(1000) select @integer=@arabia,@english='' select @trillion=@integer % 1000000000000000/1000000000000,@billion=@integer % 1000000000000/1000000000, @million=@integer % 1000000000/1000000,@thousand=(@integer % 1000000)/1000,@hundred=(@integer % 1000) if @trillion>0 set @english=@english + dbo.ThreeDigit(@trillion) + 'trillion ' if @billion>0 set @english=@english + dbo.ThreeDigit(@billion) + 'billion ' if @million>0 set @english=@english + dbo.ThreeDigit(@million) + 'million ' if @thousand>0 set @english=@english + dbo.ThreeDigit(@thousand) + 'thousand ' if @hundred>0 set @english=@english + dbo.ThreeDigit(@hundred) if @english='' set @english='zero ' if @arabia-@integer>0.000000000 begin declare @decimal decimal(18,17) select @english=@english+'point ',@decimal=@arabia-@integer while @decimal>0.0 begin select @english=@english+e+' ' from @atoe where cast(@decimal*10 as int)=a set @decimal=@decimal*10-cast(@decimal*10 as int) end end return @english END GO -- ============================================= -- Author: qianjin036a -- Create date: 06/14/2008 02:27:17 -- Description: Three Digit Arabic numerals to English -- ============================================= CREATE FUNCTION ThreeDigit ( @integer int ) RETURNS varchar(100) WITH EXECUTE AS CALLER AS BEGIN declare @atoe table(a int,e varchar(10)) insert into @atoe select 0,'zero' union all select 1,'one' union all select 2,'two' union all select 3,'three' union all select 4,'four' union all select 5,'five' union all select 6,'six' union all select 7,'seven' union all select 8,'eight' union all select 9,'nine' union all select 10,'ten' union all select 11,'eleven' union all select 12,'twelve' union all select 13,'thirteen' union all select 14,'fourteen' union all select 15,'fifteen' union all select 16,'sixteen' union all select 17,'seventeen' union all select 18,'eighteen' union all select 19,'nineteen' union all select 20,'twenty' union all select 30,'thirty' union all select 40,'forty' union all select 50,'fifty' union all select 60,'sixty' union all select 70,'severty' union all select 80,'eighty' union all select 90,'ninety' declare @english varchar(100) set @english='' if @integer>99 begin select @english=e+' hundred ' from @atoe where @integer/100=a set @integer=@integer % 100 if @integer>0 set @english=@english+'and ' end if @integer<=20 and @integer>0 select @english=@english+e+' ' from @atoe where @integer=a if @integer>20 begin select @english=@english+e+' ' from @atoe where @integer/10*10=a set @integer=@integer % 10 if @integer>0 select @english=@english+e+' ' from @atoe where @integer=a end RETURN @english END GO select dbo.digit2english(123456789987654.321) union all select dbo.digit2english(120045080045054.8412) union all select dbo.digit2english(0.0102541) go /* --------------------------------------------------------------------- one hundred and twenty three trillion four hundred and fifty six billion seven hundred and eighty nine million nine hundred and eighty seven thousand six hundred and fifty four point three two one one hundred and twenty trillion forty five billion eighty million forty five thousand fifty four point eight four one two zero point zero one zero two five four one */
44、全角半角转换函数
--(此函数部分思路参考了CSDN上大力的转换函数) --邹建2005.01(引用请保留此信息)--*/ go --创建函数 create function SBC2DBC ( @str nvarchar(4000), --要转换的字符串 @flag bit --转换标志,0转换成半角,1转换成全角 ) returns nvarchar(4000) as begin declare @pat nvarchar(8),@step int,@i int,@spc int if @flag=0 select @pat=N'%[！-～]%',@step=-65248,@str=replace(@str,N' ',N' ') else select @pat=N'%[!-~]%',@step=65248,@str=replace(@str,N' ',N' ') set @i=patindex(@pat collate latin1_general_bin,@str) while @i>0 select @str=replace(@str,substring(@str,@i,1),nchar(unicode(substring(@str,@i,1))+@step)) ,@i=patindex(@pat collate latin1_general_bin,@str) return(@str) end --测试示例 select dbo.SBC2DBC('~~~~ca!b',1) --运行结果 /* ～～～～ｃａ！ｂ */ --附半角全角表 /* ASCII 全角字符 Unicode 半角字符 Unicode 0x20 " "空格U+3000 " "空格U+0020 0x21 ！U+ff01 ! U+0021 0x22 ＂U+ff02 " U+0022 0x23 ＃U+ff03 # U+0023 0x24 ＄U+ff04 $ U+0024 0x25 ％U+ff05 % U+0025 0x26 ＆U+ff06 & U+0026 0x27 ＇U+ff07 ' U+0027 0x28 （U+ff08 ( U+0028 0x29 ）U+ff09 ) U+0029 0x2a ＊U+ff0a * U+002a 0x2b ＋U+ff0b + U+002b 0x2c ，U+ff0c , U+002c 0x2d －U+ff0d - U+002d 0x2e ．U+ff0e . U+002e 0x2f ／U+ff0f / U+002f 0x30 ０U+ff10 0 U+0030 0x31 １U+ff11 1 U+0031 0x32 ２U+ff12 2 U+0032 0x33 ３U+ff13 3 U+0033 0x34 ４U+ff14 4 U+0034 0x35 ５U+ff15 5 U+0035 0x36 ６U+ff16 6 U+0036 0x37 ７U+ff17 7 U+0037 0x38 ８U+ff18 8 U+0038 0x39 ９U+ff19 9 U+0039 0x3a ：U+ff1a : U+003a 0x3b ；U+ff1b ; U+003b 0x3c ＜U+ff1c < U+003c 0x3d ＝U+ff1d = U+003d 0x3e ＞U+ff1e > U+003e 0x3f ？U+ff1f ? U+003f 0x40 ＠U+ff20 @ U+0040 0x41 ＡU+ff21 A U+0041 0x42 ＢU+ff22 B U+0042 0x43 ＣU+ff23 C U+0043 0x44 ＤU+ff24 D U+0044 0x45 ＥU+ff25 E U+0045 0x46 ＦU+ff26 F U+0046 0x47 ＧU+ff27 G U+0047 0x48 ＨU+ff28 H U+0048 0x49 ＩU+ff29 I U+0049 0x4a ＪU+ff2a J U+004a 0x4b ＫU+ff2b K U+004b 0x4c ＬU+ff2c L U+004c 0x4d ＭU+ff2d M U+004d 0x4e ＮU+ff2e N U+004e 0x4f ＯU+ff2f O U+004f 0x50 ＰU+ff30 P U+0050 0x51 ＱU+ff31 Q U+0051 0x52 ＲU+ff32 R U+0052 0x53 ＳU+ff33 S U+0053 0x54 ＴU+ff34 T U+0054 0x55 ＵU+ff35 U U+0055 0x56 ＶU+ff36 V U+0056 0x57 ＷU+ff37 W U+0057 0x58 ＸU+ff38 X U+0058 0x59 ＹU+ff39 Y U+0059 0x5a ＺU+ff3a Z U+005a 0x5b ［U+ff3b [ U+005b 0x5c ＼U+ff3c / U+005c 0x5d ］U+ff3d ] U+005d 0x5e ＾U+ff3e ^ U+005e 0x5f ＿U+ff3f _ U+005f 0x60 ｀U+ff40 ` U+0060 0x61 ａU+ff41 a U+0061 0x62 ｂU+ff42 b U+0062 0x63 ｃU+ff43 c U+0063 0x64 ｄU+ff44 d U+0064 0x65 ｅU+ff45 e U+0065 0x66 ｆU+ff46 f U+0066 0x67 ｇU+ff47 g U+0067 0x68 ｈU+ff48 h U+0068 0x69 ｉU+ff49 i U+0069 0x6a ｊU+ff4a j U+006a 0x6b ｋU+ff4b k U+006b 0x6c ｌU+ff4c l U+006c 0x6d ｍU+ff4d m U+006d 0x6e ｎU+ff4e n U+006e 0x6f ｏU+ff4f o U+006f 0x70 ｐU+ff50 p U+0070 0x71 ｑU+ff51 q U+0071 0x72 ｒU+ff52 r U+0072 0x73 ｓU+ff53 s U+0073 0x74 ｔU+ff54 t U+0074 0x75 ｕU+ff55 u U+0075 0x76 ｖU+ff56 v U+0076 0x77 ｗU+ff57 w U+0077 0x78 ｘU+ff58 x U+0078 0x79 ｙU+ff59 y U+0079 0x7a ｚU+ff5a z U+007a 0x7b ｛U+ff5b { U+007b 0x7c ｜U+ff5c | U+007c 0x7d ｝U+ff5d } U+007d 0x7e ～U+ff5e ~ U+007e */
45、返回两个时间范围内的一个随机时间
/****************************** * 函数名：RandDateTime * 作用： 返回两个时间范围内的一个随机时间 * Author: 兰习刚 * Date: 2009-11-30 *******************************/ go --创建函数 create Function RandDateTime ( @RandNum Decimal(38,18),--0-1之际随机数值建议Rand() @StartTime DateTime, --第一个时间 @EndTime DateTime --第二个时间 ) Returns DateTime As Begin Declare @HourDiff Decimal(38,18) --两个时间之间的小时差值 Declare @MsPartDiff Decimal(38,18) --毫秒部分的差值 Declare @SmallDate DateTime Declare @ReturnDateTime DateTime /*取各部分差值*/ Set @HourDiff = DateDiff(hh,@StartTime,@EndTime) Set @MsPartDiff = Abs(DateDiff(ms,DateAdd(hh,@HourDiff,@StartTime),@EndTime)) Select @SmallDate=(Case When @HourDiff>0 Then @StartTime Else @EndTime End) --取较小的时间 Set @HourDiff = Abs(@HourDiff) ActionLable: Declare @HourDecimal Decimal(38,18) --小时的小数部分 Declare @HourString varchar(200) Set @HourDiff = @HourDiff * @RandNum Set @HourString = CONVERT(VARCHAR(200),@HourDiff) Set @HourString = SubString(@HourString,CharIndex('.',@HourString)+1,Len(@HourString)) Set @HourString = '0.' + @HourString Set @HourDecimal = Convert(Decimal(38,18),@HourString) --获得小时的小数部分 Set @MsPartDiff = (@MsPartDiff + @HourDecimal * 3600*1000) * @RandNum /*毫秒差值 由于之前@MsPartDiff是两个时间小时之后的毫秒差值 @HourDecimal * 3600*1000 有小时的小数部分的毫秒差值不会大于小时 毫秒不会溢出 */ Set @ReturnDateTime = DateAdd(hh,@HourDiff,@SmallDate) Set @ReturnDateTime = DateAdd(ms,@MsPartDiff,@ReturnDateTime) Return @ReturnDateTime End --测试示例 select dbo.RandDateTime(Rand(),'2011-03-21 00:00:00.000','2011-03-21 23:59:00.000') go 10 --运行结果 /* ----------------------- 2011-03-21 16:44:58.990 (1 row(s) affected) ----------------------- 2011-03-21 00:00:33.313 (1 row(s) affected) ----------------------- 2011-03-21 15:04:58.777 (1 row(s) affected) ----------------------- 2011-03-21 06:32:21.347 (1 row(s) affected) ----------------------- 2011-03-21 15:11:51.047 (1 row(s) affected) ----------------------- 2011-03-21 14:39:23.597 (1 row(s) affected) ----------------------- 2011-03-21 07:24:17.247 (1 row(s) affected) ----------------------- 2011-03-21 06:15:49.653 (1 row(s) affected) ----------------------- 2011-03-21 02:06:14.757 (1 row(s) affected) ----------------------- 2011-03-21 10:49:18.370 (1 row(s) affected) */
46、获取元素个数的函数
go -- 创建函数(作者：csdn邹建) create function getstrarrlength (@str varchar(8000)) returns int as begin declare @int_return int declare @start int declare @next int declare @location int select @str =','+ @str +',' select @str=replace(@str,',,',',') select @start =1 select @next =1 select @location = charindex(',',@str,@start) while (@location <>0) begin select @start = @location +1 select @location = charindex(',',@str,@start) select @next =@next +1 end select @int_return = @next-2 return @int_return end -- 测试示例 SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d') --运行结果 /* 8 */ /* 说明： 我开始考虑直接看逗号的个数，用replace替换逗号，求长度差就可以了，但是这里这个函数两个逗号相邻做了处理。 */
47、获取指定索引的值的函数
go --创建函数(作者：csdn邹建) create function getstrofindex (@str varchar(8000),@index int =0) returns varchar(8000) as begin declare @str_return varchar(8000) declare @start int declare @next int declare @location int select @start =1 select @next =1 --如果习惯从开始则select @next =0 select @location = charindex(',',@str,@start) while (@location <>0 and @index > @next ) begin select @start = @location +1 select @location = charindex(',',@str,@start) select @next =@next +1 end if @location =0 select @location =len(@str)+1 --如果是因为没有逗号退出，则认为逗号在字符串后 select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗号之后的位置或者就是初始值 if (@index <> @next ) select @str_return = '' --如果二者不相等，则是因为逗号太少，或者@index小于@next的初始值。 return @str_return end --测试示例 SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',4) --运行结果 /* 4 */ --备注：类似功能的函数happyflystone （无枪狙击手）也写过一个，参数上做了扩展，可以定义分隔符了，在【叶子函数分享十六】我曾经发过。
48、根据年得到所有星期日的日期
go --创建函数 create function GetWeekDays(@year int) returns @t table (星期天varchar(20)) as begin insert @t select substring(convert(varchar,dateadd(day,x,col),120),1,10) from ( select cast(cast(@year as varchar(4))+'-1-1' as datetime) as col )a cross join ( select top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x from(select 0 i union all select 1) b0 cross join(select 0 i union all select 2) b1 cross join(select 0 i union all select 4) b2 cross join(select 0 i union all select 8) b3 cross join(select 0 i union all select 16) b4 cross join(select 0 i union all select 32) b5 cross join(select 0 i union all select 64) b6 cross join(select 0 i union all select 128) b7 cross join(select 0 i union all select 256) b8 order by 1 )b where datepart(dw,dateadd(day,x,col))=1 return end --测试示例 select * from dbo.GetWeekDays(2011) --运行结果 /* 星期天 -------------------- 2011-01-02 2011-01-09 2011-01-16 2011-01-23 2011-01-30 2011-02-06 2011-02-13 2011-02-20 2011-02-27 2011-03-06 2011-03-13 2011-03-20 2011-03-27 2011-04-03 2011-04-10 2011-04-17 2011-04-24 2011-05-01 2011-05-08 2011-05-15 2011-05-22 2011-05-29 2011-06-05 2011-06-12 2011-06-19 2011-06-26 2011-07-03 2011-07-10 2011-07-17 2011-07-24 2011-07-31 2011-08-07 2011-08-14 2011-08-21 2011-08-28 2011-09-04 2011-09-11 2011-09-18 2011-09-25 2011-10-02 2011-10-09 2011-10-16 2011-10-23 2011-10-30 2011-11-06 2011-11-13 2011-11-20 2011-11-27 2011-12-04 2011-12-11 2011-12-18 2011-12-25 (52 row(s) affected) */
49、生成两个时间之间的所有日期
--改写liangCK的部分代码为函数 --创建函数 create function generateTime ( @begin_date datetime, @end_date datetime ) returns @t table(date datetime) as begin with maco as ( select @begin_date AS date union all select date+1 from maco where date+1 <=@end_date ) insert into @t select * from maco option(maxrecursion 0); return end go --测试示例 select * from dbo.generateTime('2009-01-01','2009-01-10') --运行结果 /* date ----------------------- 2009-01-01 00:00:00.000 2009-01-02 00:00:00.000 2009-01-03 00:00:00.000 2009-01-04 00:00:00.000 2009-01-05 00:00:00.000 2009-01-06 00:00:00.000 2009-01-07 00:00:00.000 2009-01-08 00:00:00.000 2009-01-09 00:00:00.000 2009-01-10 00:00:00.000 */ go --第二版 --创建函数 create function generateTimeV2 ( @begin_date datetime, @end_date datetime ) returns @t table(date datetime) as begin insert into @t select dateadd(dd,number,@begin_date) AS date from master..spt_values where type='p' and dateadd(dd,number,@begin_date)<=@end_date return end --测试示例 select * from dbo.generateTimeV2('2009-01-01','2009-01-10') --运行结果 /* date ----------------------- 2009-01-01 00:00:00.000 2009-01-02 00:00:00.000 2009-01-03 00:00:00.000 2009-01-04 00:00:00.000 2009-01-05 00:00:00.000 2009-01-06 00:00:00.000 2009-01-07 00:00:00.000 2009-01-08 00:00:00.000 2009-01-09 00:00:00.000 2009-01-10 00:00:00.000 (10 row(s) affected) */
50、无序字符比较函数
go --创建函数(第一版) create function get_orderstr(@str varchar(8000)) returns varchar(8000) as begin set @str=rtrim(@str) declare @tb table(s varchar(1),a int) while len(@str)>0 begin insert into @tb select left(@str,1),ascii(left(@str,1)) set @str=right(@str,len(@str)-1) end declare @sql varchar(8000) select @sql=isnull(@sql+'','')+s from @tb order by a return isnull(@sql,'') end --测试示例 if(dbo.get_orderstr('abc')=dbo.get_orderstr('acb')) print '相同' else print '不同' --运行结果 /* 相同 */ --第二版 /* * 功能：不按先后顺序比较字符串序列是否相同 * * 适用：SQL Server 2000 / SQL Server 2005 * * 返回：相同不相同 * * 作者：Flystone * * 描述：学习Limpire(昨夜小楼)的方法后做一个动态SQL的* */ go --创建存储过程(这个不是函数) CREATE proc sp_CompareString @Str1 varchar(100), @Str2 varchar(100), @Split varchar(10), @ret int output AS BEGIN declare @Len int, @Sub varchar(100) if @Str1 = @Str2 return(1) if len(@Str1) <> len(@Str2) or len(replace(@Str1, @Split, '')) <> len(replace(@Str2, @Split, '')) begin set @ret = 0 return end set @str1 = 'select '''+replace(@str1,@Split,''' as col union all select ''')+'''' set @str2 = 'select '''+replace(@str2,@Split,''' as col union all select ''')+'''' declare @s nvarchar(4000) set @s = ' if exists(select 1 from ('+@str1+') a where not exists(select 1 from ('+@str2+') b where a.col = b.col) or exists(select 1 from ('+@str2+') a where not exists(select 1 from ('+@str1+') b where a.col = b.col) )) select @ret = 0 else select @ret = 1' exec sp_executesql @s,N'@ret int output',@ret output END GO --测试示例 declare @ret int exec sp_CompareString 'a,b,c', 'b,c,a', ',',@ret out select @ret exec sp_CompareString 'a,b,c', 'b,c,c,a', ',',@ret out select @ret drop proc sp_CompareString go --第三版 /* * * * * * * * * * * * * * * * * * * * * * * * 功能：不按先后顺序比较字符串序列是否相同* * 适用：SQL Server 2000 / SQL Server 2005 * * 返回：相同不相同 * * 作者：Limpire(昨夜小楼) * * * * * * * * * * * * * * * * * * * * * * * */ --创建函数 CREATE FUNCTION fn_CompareString ( @Str1 varchar(100), @Str2 varchar(100), @Split varchar(10) ) RETURNS bit AS BEGIN declare @Len int, @Sub varchar(100) if @Str1 = @Str2 return(1) if len(@Str1) <> len(@Str2) or len(replace(@Str1, @Split, '')) <> len(replace(@Str2, @Split, '')) return(0) select @Len = len(@Split), @Str1 = @Split + @Str1 + @Split, @Str2 = @Split + @Str2 + @Split while charindex(@Split, @Str1, @Len + 1) > 0 begin set @Sub = left(@Str1, charindex(@Split, @Str1, @Len + 1) + @Len - 1) if charindex(@Sub, @Str2) = 0 return(0) while charindex(@Sub, @Str1) > 0 set @Str1 = replace(@Str1, @Sub, ',') while charindex(@Sub, @Str2) > 0 set @Str2 = replace(@Str2, @Sub, ',') if len(@Str1)<>len(@Str2) return(0) end return(1) END GO --测试示例 select dbo.fn_CompareString('a,b,c', 'b,c,a', ',') select dbo.fn_CompareString('a,b,c', 'b,c,c,a', ',') --运行结果 /* 1 0 */
51、在SQL SERVER中实现RSA加解密函数（第一版）
/*************************************************** 作者：herowang(让你望见影子的墙） 日期：2010.1.1 注： 转载请保留此信息 更多内容，请访问我的博客：blog.csdn.net/herowang ****************************************************/ 一、RSA算法原理 RSA算法非常简单，概述如下： 找两素数p和q 取n=p*q 取t=(p-1)*(q-1) 取任何一个数e,要求满足e<t并且e与t互素（就是最大公因数为） 取d*e%t==1 这样最终得到三个数：n d e 设消息为数M (M <n) 设c=(M**d)%n就得到了加密后的消息c 设m=(c**e)%n则m == M，从而完成对c的解密。 注：**表示次方,上面两式中的d和e可以互换。 在对称加密中： n d两个数构成公钥，可以告诉别人； n e两个数构成私钥，e自己保留，不让任何人知道。 给别人发送的信息使用e加密，只要别人能用d解开就证明信息是由你发送的，构成了签名机制。 别人给你发送信息时使用d加密，这样只有拥有e的你能够对其解密。 rsa的安全性在于对于一个大数n，没有有效的方法能够将其分解从而在已知n d的情况下无法获得e；同样在已知n e的情况下无法求得d。 以上内容出自原文出处http://www.xfocus.net/articles/200503/778.html 二、使用T-SQL实现RSA算法 --判断是否为素数 if object_id('f_pnumtest') is not null drop function f_isPrimeNum go create function [dbo].[f_isPrimeNum] (@p int) returns bit begin declare @flg bit,@i int select @flg=1, @i=2 while @i<=sqrt(@p) begin if(@p%@i=0 ) begin set @flg=0 break end set @i=@i+1 end return @flg end --判断两个数是否互素，首先要选取两个互素的数 if object_id('f_isNumsPrime') is not null drop function f_isNumsPrime go create function f_isNumsPrime (@num1 int,@num2 int) returns bit begin declare @tmp int,@flg bit set @flg=1 while (@num2%@num1<>0) begin select @tmp=@num1,@num1=@num2%@num1,@num2=@tmp end if @num1=1 set @flg=0 return @flg end --产生密钥对 if object_id('p_createKey1') is not null drop proc p_createKey1 go create proc p_createKey1 @p int,@q int as begin declare @n bigint,@t bigint,@flag int,@d int if dbo.f_pnumtest(@p)=0 begin print cast(@p as varchar)+'不是素数，请重新选择数据' return end if dbo.f_pnumtest(@q)=0 begin print cast(@q as varchar)+'不是素数，请重新选择数据' return end print '请从下列数据中选择其中一对，作为密钥' select @n=@p*@q,@t=(@p-1)*(@q-1) declare @e int set @e=2 while @e<@t begin if dbo.f_isNUmsPrime(@e,@t)=0 begin set @d=2 while @d<@n begin if(@e*@d%@t=1) print cast(@e as varchar)+space(5)+cast(@d as varchar) set @d=@d+1 end end set @e=@e+1 end end /*加密函数说明，@key 为上一个存储过程中选择的密码中的一个,@p ,@q 产生密钥对时选择的两个数。获取每一个字符的ascii值，然后进行加密，产生个字节的位数据*/ if object_id('f_RSAEncry') is not null drop function f_RSAEncry go create function f_RSAEncry (@s varchar(100),@key int ,@p int ,@q int) returns varchar(8000) as begin declare @crypt varchar(8000) set @crypt='' while len(@s)>0 begin declare @i int,@tmp varchar(10),@k2 int,@leftchar int select @leftchar=ascii(left(@s,1)),@k2=@key,@i=1 while @k2>0 begin set @i=(@leftchar*@i)%(@p*@q) set @k2=@k2-1 end set @tmp='' select @tmp=case when @i%16 between 10 and 15 then char( @i%16+55) else cast(@i%16 as varchar) end +@tmp,@i=@i/16 from (select number from master.dbo.spt_values where type='p' and number<10 )K order by number desc set @crypt=@crypt+right(@tmp,4) set @s=stuff(@s,1,1,'') end return @crypt end --解密：@key 为一个存储过程中选择的密码对中另一个数字,@p ,@q 产生密钥对时选择的两个数 if object_id('f_RSADecry') is not null drop function f_RSADecry go create function f_RSADecry (@s varchar(100),@key int ,@p int ,@q int) returns varchar(8000) as begin declare @crypt varchar(8000) set @crypt='' while len(@s)>0 begin declare @i int select @i=sum(data1) from ( select case upper(substring(left(@s,4), number, 1)) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substring(left(@s,4), number, 1) end* power(16, len(left(@s,4)) - number) data1 from (select number from master.dbo.spt_values where type='p')K where number <= len(left(@s,4)) ) L declare @k2 int,@j int select @k2=@key,@j=1 while @k2>0 begin set @j=(@i*@j)%(@p*@q) set @k2=@k2-1 end set @crypt=@crypt+char(@j) set @s=stuff(@s,1,4,'') end return @crypt end 三、在SQL SERVER中的使用 --【测试】 if object_id('tb') is not null drop table tb go create table tb(id int identity(1,1),col varchar(100)) go insert into tb values(dbo.f_RSAEncry('RSA',63,47,59)) select * from tb --运行结果： /* id col ----------- ------------ 1 069505EE02F3 */ select id,col=dbo.f_RSADecry(col,847,47,59) from tb --运行结果： /* id col ----------- ----------- 1 RSA */ 四、目前版本函数的缺点 1、目前只能对ascii符号进行加密，对unicode尚不支持。 2、在选取的素数都比较小，所以密钥空间比较小，而实际应用中选取的素数都会非常的大，不容易破解。但是对于一些基础的加密还能够使用。 3、如果一次加密觉得安全性不够的话，可以进行重复加密（即进行多次加密），两次的密钥最好不相同。 例如：insert into tb values(dbo.f_RSAEncry(dbo.f_RSAEncry('RSA',63,47,59),23,11,17)) 那么解密的时候，按照加密的逆序进行解密： select id,col=dbo.f_RSADecry(dbo.f_RSADecry(col,7,11,17),847,47,59) from tb 4、如果选取的数字比较大，那么在进行加密的时候，生成的进制密文最好使用个字节或者更多。
52、在SQL SERVER中实现RSA加解密函数（第二版）
/*************************************************** 作者：herowang(让你望见影子的墙） 日期：2010.1.5 注： 转载请保留此信息 更多内容，请访问我的博客：blog.csdn.net/herowang ****************************************************/ /* 本次修改增加了unicode的支持，但是加密后依然显示为进制数据，因为进行RSA加密后所得到的unicode编码是无法显示的，所以密文依然采用进制数据显示。 需要特别注意：如果要对中文进行加密，那么所选取的两个素数要比较大，两个素数的成绩最好要大于，即大于unicode的最大编码值 另外修改了第一个版本的部分函数名称 */ 在SQL SERVER中实现RSA加密算法 --判断是否为素数 if object_id('f_primeNumTest') is not null drop function f_primeNumTest go create function [dbo].[f_primeNumTest] (@p int) returns bit begin declare @flg bit,@i int select @flg=1, @i=2 while @i<=sqrt(@p) begin if(@p%@i=0 ) begin set @flg=0 break end set @i=@i+1 end return @flg end go --测试示例： select [dbo].[f_primeNumTest](23)--1 select [dbo].[f_primeNumTest](24)--0 select [dbo].[f_primeNumTest](25)--0 select [dbo].[f_primeNumTest](26)--0 select [dbo].[f_primeNumTest](27)--0 --判断两个数是否互素 if object_id('f_isNumsPrime') is not null drop function f_isNumsPrime go create function f_isNumsPrime (@num1 int,@num2 int) returns bit begin declare @tmp int,@flg bit set @flg=1 while (@num2%@num1<>0) begin select @tmp=@num1,@num1=@num2%@num1,@num2=@tmp end if @num1=1 set @flg=0 return @flg end go --产生密钥对 if object_id('p_createKey') is not null drop proc p_createKey go create proc p_createKey @p int,@q int as begin declare @n bigint,@t bigint,@flag int,@d int if dbo.f_primeNumTest(@p)=0 begin print cast(@p as varchar)+'不是素数，请重新选择数据' return end if dbo.f_primeNumTest(@q)=0 begin print cast(@q as varchar)+'不是素数，请重新选择数据' return end print '请从下列数据中选择其中一对，作为密钥' select @n=@p*@q,@t=(@p-1)*(@q-1) declare @e int set @e=2 while @e<@t begin if dbo.f_isNumsPrime(@e,@t)=0 begin set @d=2 while @d<@n begin if(@e*@d%@t=1) print cast(@e as varchar)+space(5)+cast(@d as varchar) set @d=@d+1 end end set @e=@e+1 end end /*加密函数说明，@key 为上一个存储过程中选择的密码中的一个,@p ,@q 产生密钥对时选择的两个数。获取每一个字符的unicode值，然后进行加密，产生个字节的位数据*/ if object_id('f_RSAEncry') is not null drop function f_RSAEncry go create function f_RSAEncry (@s varchar(100),@key int ,@p int ,@q int) returns nvarchar(4000) as begin declare @crypt varchar(8000) set @crypt='' while len(@s)>0 begin declare @i bigint,@tmp varchar(10),@k2 int,@leftchar int select @leftchar=unicode(left(@s,1)),@k2=@key/2,@i=1 while @k2>0 begin set @i=(cast(power(@leftchar,2) as bigint)*@i)%(@p*@q) set @k2=@k2-1 end set @i=(@leftchar*@i)%(@p*@q) set @tmp='' select @tmp=case when @i%16 between 10 and 15 then char( @i%16+55) else cast(@i%16 as varchar) end +@tmp,@i=@i/16 from (select number from master.dbo.spt_values where type='p' and number<10 )K order by number desc set @crypt=@crypt+right(@tmp,6) set @s=stuff(@s,1,1,'') end return @crypt end --解密：@key 为一个存储过程中选择的密码对中另一个数字,@p ,@q 产生密钥对时选择的两个数 if object_id('f_RSADecry') is not null drop function f_RSADecry go create function f_RSADecry (@s nvarchar(4000),@key int ,@p int ,@q int) returns nvarchar(4000) as begin declare @crypt varchar(8000) set @crypt='' while len(@s)>0 begin declare @leftchar bigint select @leftchar=sum(data1) from (select case upper(substring(left(@s,6), number, 1)) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substring(left(@s,6), number, 1) end* power(16, len(left(@s,6)) - number) data1 from (select number from master.dbo.spt_values where type='p')K where number <= len(left(@s,6)) ) L declare @k2 int,@j bigint select @k2=@key/2,@j=1 while @k2>0 begin set @j=(cast(power(@leftchar,2)as bigint)*@j)%(@p*@q) set @k2=@k2-1 end set @j=(@leftchar*@j)%(@p*@q) set @crypt=@crypt+nchar(@j) set @s=stuff(@s,1,6,'') end return @crypt end 使用方法： 1、先使用p_createkey生成一对密钥，参数为两个参数 2、调用相应进行加密、解密 --【测试】 if object_id('tb') is not null drop table tb go create table tb(id int identity(1,1),col varchar(100)) go insert into tb values(dbo.f_RSAEncry('中国人',779,1163,59)) insert into tb values(dbo.f_RSAEncry('Chinese',779,1163,59)) select * from tb --运行结果 /* id col ----------- --------------------------------------------- 1 00359B00E6E000EAF5 2 01075300931B0010A4007EDC004B340074A6004B34 */ select * ,解密后=dbo.f_RSADecry(col,35039,1163,59)from tb --测试示例 /* id col 解密后 ----------- ------------------------------------------- ----------- 1 00359B00E6E000EAF5 中国人 2 01075300931B0010A4007EDC004B340074A6004B34 Chinese */
53、输出指定格式的数据列
-- ============================================= -- Author: maco_wang -- Create date: 2011-03-30 -- Description: -- 需求贴：http://topic.csdn.net/u/20110330/10/dd155c82-e156-49df-9b5a-65bdbb0bf3ab.html -- ============================================= 前记： Csdn上看到一帖子，要求如下： 编程一个函数实现功能，给出n,打印1-n,例如1 22 33 444 555 666 7777 8888 9999 10101010 就是要 1个1位： 1 2个2位： 22 33 3个3位： 444 555 666 4个4位： 7777 8888 9999 10101010 .... 虽然是.NET技术-ASP.NET板块的帖子，但是思路都是一样的，用SQL写了一下： create function PrintN(@n int) returns @table table (id bigint) as begin declare @i bigint;set @i=1 declare @j bigint;declare @k bigint; while (@i<=@n) begin set @j=0;set @k=0 while @j<@i begin set @j=@j+@k;set @k=@k+1 end insert into @table select replicate(@i,@k-1) set @i=@i+1 end return end --查看结果 select * from dbo.PrintN(20) /* 1 22 33 444 555 666 7777 8888 9999 10101010 1111111111 1212121212 1313131313 1414141414 1515151515 161616161616 171717171717 181818181818 191919191919 202020202020 */
54、汉字转拼音函数
/* ------------------------------------------------------------- 函数: fn_GetPinyin 描述: 汉字转拼音(无数据表版) 使用: dbo.fn_GetPinyin('中华人民共和国') = zhonghuarenmingongheguo 作者: 流香羽(改编：Tony) 博客: http://hi.baidu.com/流香羽 ------------------------------------------------------------- */ --创建函数 IF OBJECT_ID('[fn_GetPinyin]') IS NOT NULL DROP FUNCTION [fn_GetPinyin] GO create function [dbo].[fn_GetPinyin](@words nvarchar(2000)) returns varchar(8000) as begin declare @word nchar(1) declare @pinyin varchar(8000) declare @i int declare @words_len int declare @unicode int set @i = 1 set @words = ltrim(rtrim(@words)) set @words_len = len(@words) while (@i <= @words_len) --循环取字符 begin set @word = substring(@words, @i, 1) set @unicode = unicode(@word) set @pinyin = ISNULL(@pinyin +SPACE(1),'')+ (case when unicode(@word) between 19968 and 19968+20901 then (select top 1 py from ( select 'a' as py,N'厑' as word union all select 'ai',N'靉' union all select 'an',N'黯' union all select 'ang',N'醠' union all select 'ao',N'驁' union all select 'ba',N'欛' union all select 'bai',N'瓸' --韛兡瓸 union all select 'ban',N'瓣' union all select 'bang',N'鎊' union all select 'bao',N'鑤' union all select 'bei',N'鐾' union all select 'ben',N'輽' union all select 'beng',N'鏰' union all select 'bi',N'鼊' union all select 'bian',N'變' union all select 'biao',N'鰾' union all select 'bie',N'彆' union all select 'bin',N'鬢' union all select 'bing',N'靐' union all select 'bo',N'蔔' union all select 'bu',N'簿' union all select 'ca',N'囃' union all select 'cai',N'乲' --縩乲 union all select 'can',N'爘' union all select 'cang',N'賶' union all select 'cao',N'鼜' union all select 'ce',N'簎' union all select 'cen',N'笒' union all select 'ceng',N'乽' --硛硳岾猠乽 union all select 'cha',N'詫' union all select 'chai',N'囆' union all select 'chan',N'顫' union all select 'chang',N'韔' union all select 'chao',N'觘' union all select 'che',N'爡' union all select 'chen',N'讖' union all select 'cheng',N'秤' union all select 'chi',N'鷘' union all select 'chong',N'銃' union all select 'chou',N'殠' union all select 'chu',N'矗' union all select 'chuai',N'踹' union all select 'chuan',N'鶨' union all select 'chuang',N'愴' union all select 'chui',N'顀' union all select 'chun',N'蠢' union all select 'chuo',N'縒' union all select 'ci',N'嗭' --賜嗭 union all select 'cong',N'謥' union all select 'cou',N'輳' union all select 'cu',N'顣' union all select 'cuan',N'爨' union all select 'cui',N'臎' union all select 'cun',N'籿' union all select 'cuo',N'錯' union all select 'da',N'橽' union all select 'dai',N'靆' union all select 'dan',N'饏' union all select 'dang',N'闣' union all select 'dao',N'纛' union all select 'de',N'的' union all select 'den',N'扽' union all select 'deng',N'鐙' union all select 'di',N'螮' union all select 'dia',N'嗲' union all select 'dian',N'驔' union all select 'diao',N'鑃' union all select 'die',N'嚸' --眰嚸 union all select 'ding',N'顁' union all select 'diu',N'銩' union all select 'dong',N'霘' union all select 'dou',N'鬭' union all select 'du',N'蠹' union all select 'duan',N'叾' --籪叾 union all select 'dui',N'譵' union all select 'dun',N'踲' union all select 'duo',N'鵽' union all select 'e',N'鱷' union all select 'en',N'摁' union all select 'eng',N'鞥' union all select 'er',N'樲' union all select 'fa',N'髮' union all select 'fan',N'瀪' union all select 'fang',N'放' union all select 'fei',N'靅' union all select 'fen',N'鱝' union all select 'feng',N'覅' union all select 'fo',N'梻' union all select 'fou',N'鴀' union all select 'fu',N'猤' --鰒猤 union all select 'ga',N'魀' union all select 'gai',N'瓂' union all select 'gan',N'灨' union all select 'gang',N'戇' union all select 'gao',N'鋯' union all select 'ge',N'獦' union all select 'gei',N'給' union all select 'gen',N'搄' union all select 'geng',N'堩' --亙堩啹喼嗰 union all select 'gong',N'兣' --熕贑兝兣 union all select 'gou',N'購' union all select 'gu',N'顧' union all select 'gua',N'詿' union all select 'guai',N'恠' union all select 'guan',N'鱹' union all select 'guang',N'撗' union all select 'gui',N'鱥' union all select 'gun',N'謴' union all select 'guo',N'腂' union all select 'ha',N'哈' union all select 'hai',N'饚' union all select 'han',N'鶾' union all select 'hang',N'沆' union all select 'hao',N'兞' union all select 'he',N'靏' union all select 'hei',N'嬒' union all select 'hen',N'恨' union all select 'heng',N'堼' --堼囍 union all select 'hong',N'鬨' union all select 'hou',N'鱟' union all select 'hu',N'鸌' union all select 'hua',N'蘳' union all select 'huai',N'蘾' union all select 'huan',N'鰀' union all select 'huang',N'鎤' union all select 'hui',N'顪' union all select 'hun',N'諢' union all select 'huo',N'夻' union all select 'ji',N'驥' union all select 'jia',N'嗧' union all select 'jian',N'鑳' union all select 'jiang',N'謽' union all select 'jiao',N'釂' union all select 'jie',N'繲' union all select 'jin',N'齽' union all select 'jing',N'竸' union all select 'jiong',N'蘔' union all select 'jiu',N'欍' union all select 'ju',N'爠' union all select 'juan',N'羂' union all select 'jue',N'钁' union all select 'jun',N'攈' union all select 'ka',N'鉲' union all select 'kai',N'乫' --鎎乫 union all select 'kan',N'矙' union all select 'kang',N'閌' union all select 'kao',N'鯌' union all select 'ke',N'騍' union all select 'ken',N'褃' union all select 'keng',N'鏗' --巪乬唟厼怾 union all select 'kong',N'廤' union all select 'kou',N'鷇' union all select 'ku',N'嚳' union all select 'kua',N'骻' union all select 'kuai',N'鱠' union all select 'kuan',N'窾' union all select 'kuang',N'鑛' union all select 'kui',N'鑎' union all select 'kun',N'睏' union all select 'kuo',N'穒' union all select 'la',N'鞡' union all select 'lai',N'籟' union all select 'lan',N'糷' union all select 'lang',N'唥' union all select 'lao',N'軂' union all select 'le',N'餎' union all select 'lei',N'脷' --嘞脷 union all select 'leng',N'睖' union all select 'li',N'瓈' union all select 'lia',N'倆' union all select 'lian',N'纞' union all select 'liang',N'鍄' union all select 'liao',N'瞭' union all select 'lie',N'鱲' union all select 'lin',N'轥' --轥拎 union all select 'ling',N'炩' union all select 'liu',N'咯' --瓼甅囖咯 union all select 'long',N'贚' union all select 'lou',N'鏤' union all select 'lu',N'氇' union all select 'lv',N'鑢' union all select 'luan',N'亂' union all select 'lue',N'擽' union all select 'lun',N'論' union all select 'luo',N'鱳' union all select 'ma',N'嘛' union all select 'mai',N'霢' union all select 'man',N'蘰' union all select 'mang',N'蠎' union all select 'mao',N'唜' union all select 'me',N'癦' --癦呅 union all select 'mei',N'嚜' union all select 'men',N'們' union all select 'meng',N'霥' --霿踎 union all select 'mi',N'羃' union all select 'mian',N'麵' union all select 'miao',N'廟' union all select 'mie',N'鱴' --鱴瓱 union all select 'min',N'鰵' union all select 'ming',N'詺' union all select 'miu',N'謬' union all select 'mo',N'耱' --耱乮 union all select 'mou',N'麰' --麰蟱 union all select 'mu',N'旀' union all select 'na',N'魶' union all select 'nai',N'錼' union all select 'nan',N'婻' union all select 'nang',N'齉' union all select 'nao',N'臑' union all select 'ne',N'呢' union all select 'nei',N'焾' --嫩焾 union all select 'nen',N'嫩' union all select 'neng',N'能' --莻嗯鈪銰啱 union all select 'ni',N'嬺' union all select 'nian',N'艌' union all select 'niang',N'釀' union all select 'niao',N'脲' union all select 'nie',N'钀' union all select 'nin',N'拰' union all select 'ning',N'濘' union all select 'niu',N'靵' union all select 'nong',N'齈' union all select 'nou',N'譳' union all select 'nu',N'搙' union all select 'nv',N'衄' union all select 'nue',N'瘧' union all select 'nuan',N'燶' --硸黁燶郍 union all select 'nuo',N'桛' union all select 'o',N'鞰' --毮夞乯鞰 union all select 'ou',N'漚' union all select 'pa',N'袙' union all select 'pai',N'磗' --鎃磗 union all select 'pan',N'鑻' union all select 'pang',N'胖' union all select 'pao',N'礮' union all select 'pei',N'轡' union all select 'pen',N'喯' union all select 'peng',N'喸' --浌巼闏乶喸 union all select 'pi',N'鸊' union all select 'pian',N'騙' union all select 'piao',N'慓' union all select 'pie',N'嫳' union all select 'pin',N'聘' union all select 'ping',N'蘋' union all select 'po',N'魄' union all select 'pou',N'哛' --兺哛 union all select 'pu',N'曝' union all select 'qi',N'蟿' union all select 'qia',N'髂' union all select 'qian',N'縴' union all select 'qiang',N'瓩' --羻兛瓩 union all select 'qiao',N'躈' union all select 'qie',N'籡' union all select 'qin',N'藽' union all select 'qing',N'櫦' union all select 'qiong',N'瓗' union all select 'qiu',N'糗' union all select 'qu',N'覻' union all select 'quan',N'勸' union all select 'que',N'礭' union all select 'qun',N'囕' union all select 'ran',N'橪' union all select 'rang',N'讓' union all select 'rao',N'繞' union all select 're',N'熱' union all select 'ren',N'餁' union all select 'reng',N'陾' union all select 'ri',N'馹' union all select 'rong',N'穃' union all select 'rou',N'嶿' union all select 'ru',N'擩' union all select 'ruan',N'礝' union all select 'rui',N'壡' union all select 'run',N'橍' --橍挼 union all select 'ruo',N'鶸' union all select 'sa',N'栍' --櫒栍 union all select 'sai',N'虄' --簺虄 union all select 'san',N'閐' union all select 'sang',N'喪' union all select 'sao',N'髞' union all select 'se',N'飋' --裇聓 union all select 'sen',N'篸' union all select 'seng',N'縇' --閪縇 union all select 'sha',N'霎' union all select 'shai',N'曬' union all select 'shan',N'鱔' union all select 'shang',N'緔' union all select 'shao',N'潲' union all select 'she',N'欇' union all select 'shen',N'瘮' union all select 'sheng',N'賸' union all select 'shi',N'瓧' --鰘齛兙瓧 union all select 'shou',N'鏉' union all select 'shu',N'虪' union all select 'shua',N'誜' union all select 'shuai',N'卛' union all select 'shuan',N'腨' union all select 'shuang',N'灀' union all select 'shui',N'睡' union all select 'shun',N'鬊' union all select 'shuo',N'鑠' union all select 'si',N'乺' --瀃螦乺 union all select 'song',N'鎹' union all select 'sou',N'瘶' union all select 'su',N'鷫' union all select 'suan',N'算' union all select 'sui',N'鐩' union all select 'sun',N'潠' union all select 'suo',N'蜶' union all select 'ta',N'襨' --躢襨 union all select 'tai',N'燤' union all select 'tan',N'賧' union all select 'tang',N'燙' union all select 'tao',N'畓' --討畓 union all select 'te',N'蟘' union all select 'teng',N'朰' --霯唞朰 union all select 'ti',N'趯' union all select 'tian',N'舚' union all select 'tiao',N'糶' union all select 'tie',N'餮' union all select 'ting',N'乭' --濎乭 union all select 'tong',N'憅' union all select 'tou',N'透' union all select 'tu',N'鵵' union all select 'tuan',N'褖' union all select 'tui',N'駾' union all select 'tun',N'坉' union all select 'tuo',N'籜' union all select 'wa',N'韤' union all select 'wai',N'顡' union all select 'wan',N'贎' union all select 'wang',N'朢' union all select 'wei',N'躛' union all select 'wen',N'璺' union all select 'weng',N'齆' union all select 'wo',N'齷' union all select 'wu',N'鶩' union all select 'xi',N'衋' union all select 'xia',N'鏬' union all select 'xian',N'鼸' union all select 'xiang',N'鱌' union all select 'xiao',N'斆' union all select 'xie',N'躞' union all select 'xin',N'釁' union all select 'xing',N'臖' union all select 'xiong',N'敻' union all select 'xiu',N'齅' union all select 'xu',N'蓿' union all select 'xuan',N'贙' union all select 'xue',N'瀥' union all select 'xun',N'鑂' union all select 'ya',N'齾' union all select 'yan',N'灩' union all select 'yang',N'樣' union all select 'yao',N'鑰' union all select 'ye',N'岃' --鸈膶岃 union all select 'yi',N'齸' union all select 'yin',N'檼' union all select 'ying',N'譍' union all select 'yo',N'喲' union all select 'yong',N'醟' union all select 'you',N'鼬' union all select 'yu',N'爩' union all select 'yuan',N'願' union all select 'yue',N'鸙' union all select 'yun',N'韻' union all select 'za',N'雥' union all select 'zai',N'縡' union all select 'zan',N'饡' union all select 'zang',N'臟' union all select 'zao',N'竈' union all select 'ze',N'稄' union all select 'zei',N'鱡' union all select 'zen',N'囎' union all select 'zeng',N'贈' union all select 'zha',N'醡' union all select 'zhai',N'瘵' union all select 'zhan',N'驏' union all select 'zhang',N'瞕' union all select 'zhao',N'羄' union all select 'zhe',N'鷓' union all select 'zhen',N'黮' union all select 'zheng',N'證' union all select 'zhi',N'豒' union all select 'zhong',N'諥' union all select 'zhou',N'驟' union all select 'zhu',N'鑄' union all select 'zhua',N'爪' union all select 'zhuai',N'跩' union all select 'zhuan',N'籑' union all select 'zhuang',N'戅' union all select 'zhui',N'鑆' union all select 'zhun',N'稕' union all select 'zhuo',N'籱' union all select 'zi',N'漬' --漬唨 union all select 'zong',N'縱' union all select 'zou',N'媰' union all select 'zu',N'謯' union all select 'zuan',N'攥' union all select 'zui',N'欈' union all select 'zun',N'銌' union all select 'zuo',N'咗') t where word >= @word collate Chinese_PRC_CS_AS_KS_WS order by word collate Chinese_PRC_CS_AS_KS_WS ASC) else @word end) set @i = @i + 1 end return @pinyin END GO --测试示例 SELECT dbo.fn_GetPinyin('欢迎访问叶子的博客') --运行结果 /* huan ying fang wen ye zi de bo ke */
55、数字转IP地址函数
--------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-06-19 10:34:31 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -- Blog : http://blog.csdn.net/htl258 -- Subject: 数字转IP地址函数 --------------------------------------------------------------------- --数字转IP地址函数： IF OBJECT_ID('dbo.fn_IP2Str')>0 DROP FUNCTION dbo.fn_IP2Str GO CREATE FUNCTION [dbo].[fn_IP2Str] ( @InIP BIGINT ) RETURNS NVARCHAR(15) AS BEGIN IF @InIP IS NULL RETURN '0.0.0.0' DECLARE @ip BIGINT SET @ip = @InIP SET @ip = @ip + 0x100000000 RETURN CAST(((@ip & 0xff000000) / 0x1000000) AS NVARCHAR(3)) + '.' + CAST(((@ip & 0xff0000) / 0x10000) AS NVARCHAR(3)) + '.' + CAST(((@ip & 0xff00) / 0x100) AS NVARCHAR(3)) + '.' + CAST((@ip & 0xff) AS NVARCHAR(3)) END GO --调用示例： SELECT dbo.fn_IP2Str(4294967295) /* --------------- 255.255.255.255 (1 行受影响) */ SELECT dbo.fn_IP2Str(0) /* --------------- 0.0.0.0 (1 行受影响) */ --附：以下转自邹建 --ip地址与数字相互转换的sql函数 IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[f_IP2Int]') AND xtype IN (N'FN', N'IF', N'TF') ) DROP FUNCTION [dbo].[f_IP2Int] GO /*--字符型IP 地址转换成数字IP --邹建 2004.08(引用请保留此信息)--*/ /*--调用示例 select dbo.f_IP2Int('255.255.255.255') select dbo.f_IP2Int('12.168.0.1') --*/ CREATE FUNCTION f_IP2Int ( @ip CHAR(15) ) RETURNS BIGINT AS BEGIN DECLARE @re BIGINT SET @re = 0 SELECT @re = @re+LEFT(@ip, CHARINDEX('.', @ip+'.')-1)*ID, @ip = STUFF(@ip, 1, CHARINDEX('.', @ip+'.'), '') FROM ( SELECT ID = CAST(16777216 AS BIGINT) UNION ALL SELECT 65536 UNION ALL SELECT 256 UNION ALL SELECT 1 )A RETURN(@re) END GO IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[f_Int2IP]') AND xtype IN (N'FN', N'IF', N'TF') ) DROP FUNCTION [dbo].[f_Int2IP] GO /*--数字 IP 转换成格式化 IP 地址 --邹建 2004.08(引用请保留此信息)-- */ /*--调用示例 select dbo.f_Int2IP(4294967295) select dbo.f_Int2IP(212336641) --*/ CREATE FUNCTION f_Int2IP ( @IP BIGINT ) RETURNS VARCHAR(15) AS BEGIN DECLARE @re VARCHAR(16) SET @re = '' SELECT @re = @re+'.'+CAST(@IP/ID AS VARCHAR), @IP = @IP%ID FROM ( SELECT ID = CAST(16777216 AS BIGINT) UNION ALL SELECT 65536 UNION ALL SELECT 256 UNION ALL SELECT 1 )a RETURN(STUFF(@re, 1, 1, '')) END GO select dbo.f_Int2IP(333444343) /* 19.223.244.247 */
56、对字符串进行加密解密
create view v_rand as select c=unicode(cast(round(rand()*255,0) as tinyint)) go create function f_jmstr ( @str varchar(8000), @type bit ) returns varchar(8000) /* *参数说明 *str:要加密的字符串或已经加密后的字符 *type:操作类型--0加密--解密 *返回值说明 *当操作类型为加密时(type--0):返回为加密后的str,即存放于数据库中的字符串 *当操作类型为解密时(type--1):返回为实际字符串,即加密字符串解密后的原来字符串 */ As begin declare @re varchar(8000)--返回值 declare @c int--加密字符 declare @i int /* *加密方法为原字符异或一个随机ASCII字符 */ if @type=0--加密 begin select @c=c,@re='',@i=len(@str) from v_rand while @i>0 select @re=nchar(unicode(substring(@str,@i,1))^@c^@i)+@re ,@i=@i-1 set @re=@re+nchar(@c) end else--解密 begin select @i=len(@str)-1,@c=unicode(substring(@str,@i+1,1)),@re='' while @i>0 select @re=nchar(unicode(substring(@str,@i,1))^@c^@i)+@re ,@i=@i-1 end return(@re) end go --测试 declare @tempstr varchar(20) set @tempstr=' 1 2 3aA' select '原始值:',@tempstr select '加密后:',dbo.f_jmstr(@tempstr,0) select '解密后:',dbo.f_jmstr(dbo.f_jmstr(@tempstr,0),1) --输出结果 /* 原始值: 1 2 3aA 加密后: __0'15`'17__°{1 解密后: 1 2 3aA */ 本文来自CSDN博客，转载请标明出处： http://blog.csdn.net/callzjy/archive/2004/05/21/20071.aspx
57、计算个人所得税函数
-- ============================================= -- Author: Maco_wang -- Create date: 2011-03-<Create Date,,> -- Description: 参考htl258(Tony)的思路，改写的计算个税的函数 -- ============================================= create function TaxRateOfPersonal ( @fvalue numeric(18,4) ) returns numeric(18,4) as begin declare @i numeric(18,4) declare @basetable table(id int, basemoney numeric(18,4),minvalue numeric(18,4), maxvalue numeric(18,4),taxs numeric(18,4)) insert into @basetable select 1,2000,0,1000,0.05 union all select 2,2000,1000,3000,0.1 union all select 3,2000,3000,6000,0.15 union all select 4,2000,6000,10000,0.2 union all select 5,2000,10000,15000,0.25 select @i=sum(case when @fvalue>basemoney+maxvalue then maxvalue-minvalue else @fvalue-basemoney-minvalue end *taxs) from @basetable where basemoney+minvalue<=@fvalue return @i end --测试示例 select dbo.TaxRateOfPersonal(2500) select dbo.TaxRateOfPersonal(3500) select dbo.TaxRateOfPersonal(5000) select dbo.TaxRateOfPersonal(9500) --运行结果 /* 25.0000 100.0000 250.0000 1000.0000 */
