Microsoft SQL Server 自定义函数整理大全
生活随笔
收集整理的這篇文章主要介紹了
Microsoft SQL Server 自定义函数整理大全
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
01、去除字符串中的html標記及標記中的內容
【葉子函數分享一】去除字符串中的html標記及標記中的內容 --1、創建函數 create?function?[dbo].[clearhtml]?(@maco?varchar(8000)) returns?varchar(8000)?as?begindeclare?@i?intwhile?1?=?1beginset?@i=len(@maco)set?@maco=replace(@maco,?substring(@maco,charindex('<',@maco),charindex('>',@maco)-charindex('<',@maco)+1),space(0))if?@i=len(?@maco?)breakendset?@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?οnclick=s(this)?href=http://news.baidu.com>新 聞</a><b>網 頁</b><a?οnclick=s(this)?href=http://tieba.baidu.com>貼 吧</a><a?οnclick=s(this)?href=http://zhidao.baidu.com>知 道</a><a?οnclick=s(this)?href=http://mp3.baidu.com>MP3</a><a?οnclick=s(this)?href=http://p_w_picpath.baidu.com>圖 片</a><a?οnclick=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 begindeclare?@randchar_one?nvarchar(200)declare?@randchar_two?nvarchar(200)if(charindex('<<',@maco)>0)beginset?@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)enddeclare?@i?intwhile?1?=?1beginset?@i=len(@maco)set?@maco=replace(@maco,?substring(@maco,charindex('<',@maco),charindex('>',@maco)-charindex('<',@maco)+1),space(0))if?@i=len(?@maco?)breakendset?@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)beginset?@maco=replace(@maco,'D4678B36-B958-4274-B81E-BBA636CFB427','<<')set?@maco=replace(@maco,'49E374CC-9E1A-4850-897C-27074DE32E7F','>>')endreturn?(@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?intdeclare?@newchar?nvarchar(200),@nn?nvarchar(300)set?@count=len(@str);set?@i=1;set?@isnull=1;set?@nn='';--end?declare--begin?whilewhile?@i<@count+1beginset?@newchar=substring(@str,@i,1)if(@isnull=1)beginset?@nn=@nn+@newchar;???if(@newchar=@split)beginset?@isnull=0;endelsebeginset?@isnull=1;end???endelsebeginif(@newchar=@split)beginset?@isnull=0;endelsebeginset?@nn=@nn+@newchar;???set?@isnull=1;end???endset?@i=@i+1;end--end?whilereturn??@nn end --2、測試示例 declare?@str?nvarchar(200) set?@str='1??2?3????4?555?6??7????7'; declare?@split?nvarchar(200) set?@split='?'; select?dbo.m_delrepeatsplit(@str,@split)?as?newchar --3、運行結果 /* newchar ------------------ 2?3?4?555?6?7?7 */03、求第一個字符串中第二個串的個數
--創建函數 create?function?[dbo].[m_count] (@str_one?nvarchar(200),??--第一個字符串@str_two?nvarchar(200)???--第二個字符串 ) returns?int?as begindeclare?@sqlcount?intselect?@sqlcount=(len(@str_one)-len(replace(@str_one,@str_two,'')))/len(@str_two) return?@sqlcount end --測試示例 select?dbo.m_count('sqlserver','e')?as?[count] --運行結果 /* count ----------- */04、綜合模糊查詢
--創建函數 create?function?[dbo].[m_fuzzyquery_v1] (@str?nvarchar(2000) )?? returns?nvarchar(2000) as?? begin??declare?@count?int,@i?int;declare?@newchar?nvarchar(200),@nn?nvarchar(300),@hh?nvarchar(200)set?@count=len(@str);set?@i=1;set?@nn='';while?@i<@count+1beginset?@newchar=substring(@str,@i,1)+'%'set?@nn=@nn+@newchar;set?@i=@i+1;endset?@hh='%'+@nnreturn?@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 begindeclare??@temp_mod?intdeclare??@i?intdeclare??@result?varchar(100)declare??@temp_x?intdeclare??@result_values?intset??@result=''set??@i=1set??@temp_x=0 while??@cardno>0beginset??@temp_mod=@cardno%16set??@cardno=@cardno/16set??@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??)+@resultend return?@result end --測試示例 select?[dbo].[hex](1808)?as?Hex --運行結果 /* Hex ---------- */ --第二版 /****************************整數轉換成進制作者：不得閑QQ:?75492895Email:?appleak46@yahoo.com.cn ****************************/ go Create?Function?IntToHex(@IntNum?int) returns?varchar(16) as begindeclare?@Mods?int,@res?varchar(16)set?@res=''while?@IntNum?<>?0beginset?@Mods?=@IntNum?%?16if?@Mods?>?9set?@res?=?Char(Ascii('A')+@Mods-10)+@reselseset?@res?=?Cast(@Mods?as?varchar(4))?+?@resset?@IntNum?=?@IntNum/16endreturn?@res end --測試示例 select?dbo.IntToHex(1808) --運行結果 /* */06、求兩個字符串中相同的漢字及字母的個數
--創建函數 create?function?[dbo].[funcomparestring] (@stra?nvarchar(200),@strb?nvarchar(200) ) returns?int?as begindeclare?@strbase?nvarchar(200)declare?@rank?intselect?@rank=0if?len(@stra)>len(@strb)select?@rank=count(*)from?funsplitchar(@strb)where?item?in(select?item?from?funsplitchar(@stra))elseselect?@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 begindeclare?@strbase?nvarchar(200)declare?@rank?intselect?@rank=0if?len(@stra)>len(@strb)select?@rank=count(*)from?funsplitchar(@strb)where?item?in(select?distinct??item?from?funsplitchar(@stra))elseselect?@rank=count(*)from(select?distinct?*?from?funsplitchar(@stra)where?item?in(select?distinct?item?from?funsplitchar(@strb)))?bbreturn?@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 begindeclare?@i?intset?@i=ceiling(@n/32.00)declare?@j?intset?@j=0declare?@k?varchar(max)set?@k=''while?@j<@ibeginselect?@k=@k+replace(cast(MacoId?as?varchar(36)),'-','')?from?vnewidset?@j=@j+1endset?@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 begindeclare?@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)?begininsert?into?@maco_wang?select?char(@number)set?@number=@number+1;if(@number=58)beginset?@number=65??????????endif(@number=91)begin?????set?@number=97???endendwhile?@maco_number<@mycountbeginselect?@my_two=id?from?@maco_wangorder?by?(select?MacoId?from?dbo.m_macoview);set?@my_one=@my_two+@my_one;set?@maco_number=@maco_number+1;endreturn?@my_one end --測試用例 select?[dbo].[m_rand](75) --運行結果 /* 5nN0w4o4VOkjacB5so2uvCuw2ZRrnBhxEi4IcsEOHzBbStKmR1p8ASH4N4XaxhDoDEtkX8bZ0CR */08、取出字符串中的漢字、字母或是數字
go --創建函數(得到字符串中的漢字) create?function?[dbo].[m_getchinese] (@chinese?nvarchar(max) ) returns?varchar(100) as beginwhile?patindex('%[^吖-咗]%',@chinese)?>?0beginset?@chinese?=?stuff(@chinese,patindex('%[^吖-咗]%',@chinese),1,N'');endreturn?@chinese end go --創建函數(得到字符串中的字母) create?function?[dbo].[m_getstr](@maco?varchar(100)) returns?varchar(max) as beginwhile?patindex('%[^a-z]%',@maco)?>?0beginset?@maco=stuff(@maco,patindex('%[^a-z]%',@maco),1,'')endreturn?@maco end go --創建函數(得到字符串中的數字) create?function?[dbo].[m_getnumber] (@mysql_one?nvarchar(200) ) returns?varchar(200) begindeclare?@mysql_two?varchar(200)select?@mysql_two=substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),patindex('%[^0-9.]%',substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),len(@mysql_one)-patindex('%[0-9.]%',@mysql_one)+1))-1)return?@mysql_two; end --測試 select?dbo.[m_getchinese]('China2009中國HRB4-1v') select?dbo.[m_getstr]('China2009中國HRB4-1v') select?dbo.[m_getnumber]('China2009中國HRB4-1v') --運行結果 /* ----------- 中國 ----------- ChinaHRBv ----------- */ --說明一下 --上面這個取數字是可以取浮點型的 select?dbo.[m_getnumber]?('字段.456A(AA)A')--正常 select?dbo.[m_getnumber]?('CHinese2.1day')--正常 select?dbo.[m_getnumber]?('Name5.01From')--正常 select?dbo.[m_getnumber]?('9898Address')--正常 select?dbo.[m_getnumber]?('aaaaaForm2.3333')--錯誤 --修正函數 go /*?取出字符串中間的數字(第二版)*/ create?function?[dbo].[m_getnumberV2.0] (@mysql_one?nvarchar(200) ) returns?varchar(200) begindeclare?@mysql_two?varchar(200)declare?@sql_one?intdeclare?@sql_two?intselect?@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=0beginselect?@mysql_two=?substring?(@mysql_one,@sql_one,len(@mysql_one)+1-@sql_one)endelsebeginselect?@mysql_two=substring?(@mysql_one,@sql_one,@sql_two-1)endreturn?@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 asreturn(select?substring(@s,number,charindex(@split,@s+@split,number)-number)as?colfrom?master..spt_valueswhere?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 begindeclare?@i?intset?@inputstr?=?rtrim(ltrim(@inputstr))set?@i?=?charindex(@seprator,?@inputstr)while?@i?>=?1begininsert?@temp?values(left(@inputstr,?@i?-?1))set?@inputstr?=?substring(@inputstr,?@i?+?1,?len(@inputstr)?-?@i)set?@i?=?charindex(@seprator,?@inputstr)endif?@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 begindeclare?@str?varchar(32),?@len?int,?@left?varchar(32),?@right?varchar(32),@end?varchar(32)if?@n!='0'beginset?@str=?round(@dec,@n)select?@left=left(@str,charindex('.',@str)-1),@len=len(@left)-2while?@len>1beginselect?@left=stuff(@left,@len,0,','),?@len=@len-3endselect?@right=left(stuff(@str,1,charindex('.',@str),''),?@n),@len=4while?@len?<=len(@right)beginselect?@right=stuff(@right,@len,0,','),?@len=@len+4endset?@end=?@left+'.'+@rightendelsebeginset?@str=?round(@dec,@n)select?@left=left(@str,charindex('.',@str)-1),@len=len(@left)-2while?@len>1beginselect?@left=stuff(@left,@len,0,','),?@len=@len-3endselect?@right=left(stuff(@str,1,charindex('.',@str),''),?@n),@len=4while?@len?<=len(@right)beginselect?@right=stuff(@right,@len,0,','),?@len=@len+4endset?@end=?@leftendreturn?@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 begindeclare?@word?nchar(1),@py?nvarchar(4000)set?@py=''while?len(@str)>0beginset?@word=left(@str,1)set?@py?=?@py+?(case?when?unicode(@word)?between?19968?and?19968+20901then?(select?top?1?pyfrom(select?'a'?as?py,?N'驁'?as?wordunion?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'咗')?Twhere?word>=@word?collate?Chinese_PRC_CS_AS_KS_WSorder?by?py?asc)else?@wordend)set?@str=right(@str,len(@str)-1)endreturn?@PY end go --創建取漢字首字母函數(第二版) create?function?[dbo].[f_getpy_V2](@Str?varchar(500)='') returns?varchar(500) as begindeclare?@strlen?int,@return?varchar(500),@ii?intdeclare?@n?int,@c?char(1),@chn?nchar(1)select?@strlen=len(@str),@return='',@ii=0set?@ii=0while?@ii<@strlenbeginselect?@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?endfrom(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?aorder?by?chn?COLLATE?Chinese_PRC_CI_AS)?as?belse?set?@c='a'set?@return=@return+@cendreturn(@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 begindeclare?@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?31set?@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?31set?@birthday=?convert(varchar(10),substring(@idcardno,7,4)+'-'+substring(@idcardno,11,2)+'-'+substring(@idcardno,13,2),120) elseset?@birthday=nullreturn(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) elseset?@sex=0return(@sex) end --測試示例 select?dbo.[Get_sex]('222222198306043213') --運行結果(1表示男0表示女) /* */14、將×××的15位號碼升級為18位
go --創建函數(此函數來自于csdn，作者不詳) create?function?[dbo].[id15to18]?(@id15?char(15)) returns?char(18) as begindeclare?@id18?char(18)declare?@s1?as?integerdeclare?@s2?as?integerdeclare?@s3?as?integerdeclare?@s4?as?integerdeclare?@s5?as?integerdeclare?@s6?as?integerdeclare?@s7?as?integerdeclare?@s8?as?integerdeclare?@s9?as?integerdeclare?@s10?as?integerdeclare?@s11?as?integerdeclare?@s12?as?integerdeclare?@s13?as?integerdeclare?@s14?as?integerdeclare?@s15?as?integerdeclare?@s16?as?integerdeclare?@s17?as?integerdeclare?@s18?as?integerset?@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=1set?@s8=9set?@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))%11set?@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'endreturn?@id18 end --測試示例 select?[dbo].[id15to18]('222222830604321') --運行結果 /* */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 beginif?@s?is?null?return(null)begindeclare?@splitlen?intselect?@splitlen=len(@split+'A')-2endwhile?@index>1?and?charindex(@split,@s+@split)>0beginselect?@index=@index-1,@s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')endreturn(isnull(left(@s,charindex(@split,@s+@split)-1),'')) end --測試示例 select?dbo.split_str('1-2-3-4',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 beginselect?@c1?=?@c2,@i?=?@i+1select?@c2?=?substring(@bds,@i,1) if?charindex(@c2,'.0123456789')?>?0?or?(@c2?=?'-'?and?@c1?in('','*','-','+','/','('))begin???select?@c?=?@c?+?@c2???continue??endif?@c?<>?''??begin?insert?@t(s)??select?@c?select?@c?=?''?endif?charindex(@c2,')')>0begininsert?@t(s)??select?s?from?@s?where?id?>?isnull((select?max(id)?from?@s?where?s?in('(')),0)?order?by?id?descdelete?@s?where?id?>=?isnull((select?max(id)?from?@s?where?s?in('(')),0)continueendif?charindex(@c2,'+-)')>0begininsert?@t(s)??select?s?from?@s?where?id?>?isnull((select?max(id)?from?@s?where?s?in('(')),0)?order?by?id?descdelete?@s?where?id?>?isnull((select?max(id)?from?@s?where?s?in('(')),0)if?@c2?<>?')'?insert?@s(s)?select?@c2continueendif?charindex(@c2,'*/')>0begininsert?@t(s)??select?s?from?@s?where?id?>?isnull((select?max(id)?from?@s?where?s?in('(','+','-')),0)?order?by?id?descdelete?@s?where?id?>?isnull((select?max(id)?from?@s?where?s?in('(','+','-')),0)insert??@s?select?@c2continueendif?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 beginselect?@i?=?@i?+?1select?@c?=?s?from?@t?where?id?=?@iif?@c?=?'('?continueif?@c?not?in('*','-','+','/')??begin??insert?@sv(v)?select?convert(float,@c)?continue?endselect?@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?-?@v2when?'*'?then?@v1?*?@v2?when?'/'?then?@v1?/?@v2?endinsert?@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 ---------------------- */
18、人民幣小寫金額轉大寫
19、向左填充指定字符串
go --創建函數(該函數來自csdn，作者不詳) create?function?[dbo].[padleft] (@str?varchar(50),???--需要填充的字符串@totalwidth?int,????--填充后的長度@paddingchar?char(1)--填充使用的字符 ) returns?varchar(1000)??as? begindeclare?@s?varchar(100)set?@s?=?@strif?(?len(@str)?<?@totalwidth)begindeclare?@i?intdeclare?@strlen?intdeclare?@temp?varchar(100)set?@i?=?1;set?@strlen?=?@totalwidth?-?len(@str)set?@temp?=?'';while(@i?<=?@strlen?)beginset?@temp?=??@temp?+?@paddingchar;set?@i?=?@i?+?1;endset?@s?=?@temp?+?@strendreturn?(@s) endgo --測試示例 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 ------- */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?6select?dbo.padleftV2(cast(id?as?varchar),'0',10)?as?id?from?@table --運行結果 /* id ------------- */20、將整型數字轉換為大寫漢字
go --創建函數(該函數來自csdn,作者不詳) create?function?[dbo].[m_NumToChinese](@num?bigint) returns?varchar(20) as begindeclare?@result?varchar(20),@symbol?varchar(2)if?@num<0select?@symbol='負',@result='',@num=abs(@num)elseselect?@symbol='',@result=''while?@num<>0select?@result=substring('零壹貳叁肆伍陸柒捌玖拾',@num%10+1,1)+@result,@num=@num/10return?@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 begindeclare?@temp?table(a?varchar(200))declare?@i?intset?@CardString?=?rtrim(ltrim(@CardString))+','set?@i?=?charindex(',',?@CardString)while?@i?>=?1begininsert?@temp?values(left(@CardString,?@i?-?1))set?@CardString?=?substring(@CardString,?@i?+?1,?len(@CardString)?-?@i)set?@i?=?charindex(',',?@CardString)endif?exists(select?1from?(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?efrom?@temp)?awhere?@CardNo?between?s?and?e)set?@i=?1elseset?@i=?0return?@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 -----------result2 ----------- */22、根據日期返回星座
go --創建函數(CSDN?fredrickhu(小F)提供) create?function?udf_GetStar?(@?datetime) RETURNS?varchar(100) --?返回日期所屬星座 BEGINRETURN(select?max(star)from(select?'魔羯座'?as?star,1?as?[month],1?as?[day]union?all?select?'水瓶座',1,20union?all?select?'雙魚座',2,19union?all?select?'牡羊座',3,21union?all?select?'金牛座',4,20union?all?select?'雙子座',5,21union?all?select?'巨蟹座',6,22union?all?select?'獅子座',7,23union?all?select?'×××座',8,23union?all?select?'天秤座',9,23union?all?select?'天蝎座',10,24union?all?select?'射手座',11,22union?all?select?'魔羯座',12,22)?starswhere?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,20union?all?select?'雙魚座',2,19union?all?select?'牡羊座',3,21union?all?select?'金牛座',4,20union?all?select?'雙子座',5,21union?all?select?'巨蟹座',6,22union?all?select?'獅子座',7,23union?all?select?'×××座',8,23union?all?select?'天秤座',9,23union?all?select?'天蝎座',10,24union?all?select?'射手座',11,22union?all?select?'魔羯座',12,22)?starswhere?@?>=?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 BEGINDECLARE?@workday?int,@i?int,@bz?bit,@dt?datetimeIF?@dt_begin>?@dt_endSELECT?@bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dtELSESET?@bz=0SELECT?@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_endBEGINSELECT???@workday=CASE?WHEN?(@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7??BETWEEN?1?AND?5?THEN?@workday+1?ELSE?@workday?END,@dt_begin=@dt_begin+1ENDRETURN(CASE?WHEN?@bz=1?THEN?-@workday?ELSE?@workday?END) ENDGO --測試示例 select?dbo.f_WorkDay('2011-02-22','2011-03-14')?as?'工作日'--運行結果 /* 工作日 ----------- */24、根據年月生成日歷函數
go --創建函數(第一版)(作者：dobear_0922) create?function?fn_Calendar(@year?int,?@month?int) returns?nvarchar(max) as begindeclare?@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?datetimeselect?@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)>0beginif?(datepart(dw,?@start)+@@datefirst)%7?=?1select?@result?=?@result+@Enter?--是否換行select?@result?=?@result+right('???'+rtrim(day(@start)),?4),?@start?=?dateadd(d,?1,?@start)endreturn?@result endgo --測試示例 set?datefirst?3 print?dbo.fn_Calendar(2007,?12) select?dbo.fn_Calendar(2007,?12) set?datefirst?7--運行結果 /*Sun?Mon?Tue?Wed?Thu?Fri?Sat3???4???5???6???7???810??11??12??13??14??1517??18??19??20??21??2224??25??26??27??28??2931 */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 begindeclare?@a?table(id?int?identity(0,1),date?datetime)insert?into?@a(date)select?top?31?rtrim(@year)+'-'+rtrim(@month)+'-1'?from?sysobjectsupdate?@a?set?date=dateadd(dd,id,date)???insert?into?@tselectmax(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@awheremonth(date)=@monthgroup?by(case?datepart(dw,date)?when?7?then?datepart(week,date)+1?else?datepart(week,date)?end)return endgo --測試示例 set?datefirst?1 select?*?from?dbo.f_calendar(2007,12)--運行結果 /* 日???一???二???三???四???五???六 ----?----?----?----?----?----?----3????4????5????6????7????810???11???12???13???14???1517???18???19???20???21???2224???25???26???27???28???2931????????????????? */25、從第一個漢字開始分割字符串
go --創建函數 create?function?[dbo].[m_splitNchinese](@str_one?nvarchar(100)) returns?@result?table?(colone?nvarchar(20),coltwo?nvarchar(20)) as begininsert?@result?selectleft(@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 beginif?@s?is?null?return(null)declare?@new?varchar(50),@index?int,@temp?varchar(50)while?len(@s)>0beginset?@new=isnull(@new,'')+left(@s,1)set?@s=replace(@s,left(@s,1),'')endreturn?@new end--測試示例select?dbo.[m_distinctStr]('Chinese')?as?str1 select?dbo.[m_distinctStr]('張三李四李四張三劉六')?as?str2--運行結果結果 /* str1 --------- Chinesstr2 ------------ 張三李四劉六 */27、根據日期得到星期的函數
--最直接的方式就是用case?when判斷，這里用表變量來處理的 go --創建函數 create?function?[dbo].[m_getweek](@date?nvarchar(2000))?? returns?varchar(2000) AS begindeclare?@weekday?nvarchar(300)declare?@table?table?(id?int?,weekday?nvarchar(200))insert?into?@tableselect?0,'星期天'?union?select?1,'星期一'?union?select?2,'星期二'?unionselect?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 begindeclare?@returnvalue?varchar(14)declare?@setvalue?intset?@setvalue=datepart(mm,dateadd(dd,1,cast((cast(@year?as?varchar(4))+'0228')?as?datetime)))if(@setvalue=2)set?@returnvalue='閏年'elseset?@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?nulldrop?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?nulldrop?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=1set?@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)>0select?@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?''?endif?cast(@month?as?int)<10set?@outputdate=@outputdate+?case?@month?when?1?then?N'正'else?substring(@chinesenum,left(@month,1)+1,1)endelse?if?cast(@month?as?int)>=10set?@outputdate=@outputdate+?case?@month?when?'10'?then?N'十'?when?11?then?N'十一'else?N'十二'?endset?@outputdate=@outputdate?+?N'月'if?cast(@day?as?int)<10set?@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)?endelse?if?@day?between?'20'?and?'29'set?@outputdate=@outputdate+?case?@day?when?'20'?then?N'二十'?else?N'廿'?end+?case?@day?when?'20'?then?N''?elsesubstring(@chinesenum,right(@day,1)+1,1)?endelseset?@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'啊阿埃挨哎唉哀皚癌藹矮艾礙愛隘鞍氨安俺按暗岸胺案骯昂盎凹敖熬翱襖傲奧懊澳芭捌扒叭吧笆八疤巴拔跋靶把耙壩霸罷爸白柏百擺佰敗拜稗斑班搬扳般頒板版扮拌伴瓣半辦絆邦幫梆榜膀綁棒磅蚌鎊傍謗苞胞包褒剝薄雹保堡飽寶抱報暴豹鮑爆杯碑悲卑北輩背貝鋇倍狽備憊焙被奔苯本笨崩繃甭泵蹦迸逼鼻比鄙筆彼碧蓖蔽畢斃毖幣庇痹閉敝弊必辟壁臂避陛鞭邊編貶扁便變卞辨辯辮遍標彪膘表鱉憋別癟彬斌瀕濱賓擯兵冰柄丙秉餅炳病并玻菠播撥缽波博勃搏鉑箔伯帛舶脖膊渤泊駁捕卜哺補埠不布步簿部怖擦猜裁材才財睬踩采彩菜蔡餐參蠶殘慚慘燦蒼艙倉滄藏操糙槽曹草廁策側冊測層蹭插叉茬茶查碴搽察岔差詫拆柴豺攙摻蟬饞讒纏鏟產闡顫昌猖場嘗常長償腸廠敞暢唱倡超抄鈔朝嘲潮巢吵炒車扯撤掣徹澈郴臣辰塵晨忱沉陳趁襯撐稱城橙成呈乘程懲澄誠承逞騁秤吃癡持匙池遲弛馳恥齒侈尺赤翅斥熾充沖蟲崇寵抽酬疇躊稠愁籌仇綢瞅丑臭初出櫥廚躇鋤雛滁除楚礎儲矗搐觸處揣川穿椽傳船喘串瘡窗幢床闖創吹炊捶錘垂春椿醇唇淳純蠢戳綽疵茨磁雌辭慈瓷詞此刺賜次聰蔥囪匆從叢湊粗醋簇促躥篡竄摧崔催脆瘁粹淬翠村存寸磋撮搓措挫錯搭達答瘩打大呆歹傣戴帶殆代貸袋待逮怠耽擔丹單鄲撣膽旦氮但憚淡誕彈蛋當擋黨蕩檔刀搗蹈倒島禱導到稻悼道盜德得的蹬燈登等瞪凳鄧堤低滴迪敵笛狄滌翟嫡抵底地蒂第帝弟遞締顛掂滇碘點典靛墊電佃甸店惦奠淀殿碉叼雕凋刁掉吊釣調跌爹碟蝶迭諜疊丁盯叮釘頂鼎錠定訂丟東冬董懂動棟侗恫凍洞兜抖斗陡豆逗痘都督毒犢獨讀堵睹賭杜鍍肚度渡妒端短鍛段斷緞堆兌隊對墩噸蹲敦頓囤鈍盾遁掇哆多奪垛躲朵跺舵剁惰墮蛾峨鵝俄額訛娥惡厄扼遏鄂餓恩而兒耳爾餌洱二貳發罰筏伐乏閥法琺藩帆番翻樊礬釩繁凡煩反返范販犯飯泛坊芳方肪房防妨仿訪紡放菲非啡飛肥匪誹吠肺廢沸費芬酚吩氛分紛墳焚汾粉奮份忿憤糞豐封楓蜂峰鋒風瘋烽逢馮縫諷奉鳳佛否夫敷膚孵扶拂輻幅氟符伏俘服浮涪福袱弗甫撫輔俯釜斧脯腑府腐赴副覆賦復傅付阜父腹負富訃附婦縛咐噶嘎該改概鈣蓋溉干甘桿柑竿肝趕感稈敢贛岡剛鋼缸肛綱崗港杠篙皋高膏羔糕搞鎬稿告哥歌擱戈鴿胳疙割革葛格蛤閣隔鉻個各給根跟耕更庚羹埂耿梗工攻功恭龔供躬公宮弓鞏汞拱貢共鉤勾溝茍狗垢構購夠辜菇咕箍估沽孤姑鼓古蠱骨谷股故顧固雇刮瓜剮寡掛褂乖拐怪棺關官冠觀管館罐慣灌貫光廣逛瑰規圭硅歸龜閨軌鬼詭癸桂柜跪貴劊輥滾棍鍋郭國果裹過哈骸孩海氦亥害駭酣憨邯韓含涵寒函喊罕翰撼捍旱憾悍焊汗漢夯杭航壕嚎豪毫郝好耗號浩呵喝荷菏核禾和何合盒貉閡河涸赫褐鶴賀嘿黑痕很狠恨哼亨橫衡恒轟哄烘虹鴻洪宏弘紅喉侯猴吼厚候后呼乎忽瑚壺葫胡蝴狐糊湖弧虎唬護互滬戶花嘩華猾滑畫劃化話槐徊懷淮壞歡環桓還緩換患喚瘓豢煥渙宦幻荒慌黃磺蝗簧皇凰惶煌晃幌恍謊灰揮輝徽恢蛔回毀悔慧卉惠晦賄穢會燴匯諱誨繪葷昏婚魂渾混豁活伙火獲或惑霍貨禍擊圾基機畸稽積箕肌饑跡激譏雞姬績緝吉極棘輯籍集及急疾汲即嫉級擠幾脊己薊技冀季伎祭劑悸濟寄寂計記既忌際妓繼紀嘉枷夾佳家加莢頰賈甲鉀假稼價架駕嫁殲監堅尖箋間煎兼肩艱奸緘繭檢柬堿鹼揀撿簡儉剪減薦檻鑒踐賤見鍵箭件健艦劍餞漸濺澗建僵姜將漿江疆蔣槳獎講匠醬降蕉椒礁焦膠交郊澆驕嬌嚼攪鉸矯僥腳狡角餃繳絞剿教酵轎較叫窖揭接皆秸街階截劫節莖睛晶鯨京驚精粳經井警景頸靜境敬鏡徑痙靖竟競凈炯窘揪究糾玖韭久灸九酒廄救舊臼舅咎就疚鞠拘狙疽居駒菊局咀矩舉沮聚拒據巨具距踞鋸俱句懼炬劇捐鵑娟倦眷卷絹撅攫抉掘倔爵桔杰捷睫竭潔結解姐戒藉芥界借介疥誡屆巾筋斤金今津襟緊錦僅謹進靳晉禁近燼浸盡勁荊兢覺決訣絕均菌鈞軍君峻俊竣?？をE喀咖卡咯開揩楷凱慨刊堪勘坎砍看康慷糠扛抗亢炕考拷烤靠坷苛柯棵磕顆科殼咳可渴克刻客課肯啃墾懇坑吭空恐孔控摳口扣寇枯哭窟苦酷庫褲夸垮挎跨胯塊筷儈快寬款匡筐狂框礦眶曠況虧盔巋窺葵奎魁傀饋愧潰坤昆捆困括擴廓闊垃拉喇蠟臘辣啦萊來賴藍婪欄攔籃闌蘭瀾讕攬覽懶纜爛濫瑯榔狼廊郎朗浪撈勞牢老佬姥酪烙澇勒樂雷鐳蕾磊累儡壘擂肋類淚棱楞冷厘梨犁黎籬貍離漓理李里鯉禮莉荔吏栗麗厲勵礫歷利傈例俐痢立粒瀝隸力璃哩倆聯蓮連鐮廉憐漣簾斂臉鏈戀煉練糧涼梁粱良兩輛量晾亮諒撩聊僚療燎寥遼潦了撂鐐廖料列裂烈劣獵琳林磷霖臨鄰鱗淋凜賃吝拎玲菱零齡鈴伶羚凌靈陵嶺領另令溜琉榴硫餾留劉瘤流柳六龍聾嚨籠窿隆壟攏隴樓婁摟簍漏陋蘆盧顱廬爐擄鹵虜魯麓碌露路賂鹿潞祿錄陸戮驢呂鋁侶旅履屢縷慮氯律率濾綠巒攣孿灤卵亂掠略掄輪倫侖淪綸論蘿螺羅邏鑼籮騾裸落洛駱絡媽麻瑪碼螞馬罵嘛嗎埋買麥賣邁脈瞞饅蠻滿蔓曼慢漫謾芒茫盲氓忙莽貓茅錨毛矛鉚卯茂冒帽貌貿么玫枚梅酶霉煤沒眉媒鎂每美昧寐妹媚門悶們萌蒙檬盟錳猛夢孟瞇醚靡糜迷謎彌米秘覓泌蜜密冪棉眠綿冕免勉娩緬面苗描瞄藐秒渺廟妙蔑滅民抿皿敏憫閩明螟鳴銘名命謬摸摹蘑模膜磨摩魔抹末莫墨默沫漠寞陌謀牟某拇牡畝姆母墓暮幕募慕木目睦牧穆拿哪吶鈉那娜納氖乃奶耐奈南男難囊撓腦惱鬧淖呢餒內嫩能妮霓倪泥尼擬你匿膩逆溺蔫拈年碾攆捻念娘釀鳥尿捏聶孽嚙鑷鎳涅您檸獰凝寧擰濘牛扭鈕紐膿濃農弄奴努怒女暖虐瘧挪懦糯諾哦歐鷗毆藕嘔偶漚啪趴爬帕怕琶拍排牌徘湃派攀潘盤磐盼畔判叛乓龐旁耪胖拋咆刨炮袍跑泡呸胚培裴賠陪配佩沛噴盆砰抨烹澎彭蓬棚硼篷膨朋鵬捧碰坯砒霹批披劈琵毗啤脾疲皮匹痞僻屁譬篇偏片騙飄漂瓢票撇瞥拼頻貧品聘乒坪蘋萍平憑瓶評屏坡潑頗婆破魄迫粕剖撲鋪仆莆葡菩蒲埔樸圃普浦譜曝瀑期欺棲戚妻七凄漆柒沏其棋奇歧畦崎臍齊旗祈祁騎起豈乞企啟契砌器氣迄棄汽泣訖掐洽牽扦釬鉛千遷簽仟謙乾黔錢鉗前潛遣淺譴塹嵌欠歉槍嗆腔羌墻薔強搶橇鍬敲悄橋瞧喬僑巧鞘撬翹峭俏竅切茄且怯竊欽侵親秦琴勤芹擒禽寢沁青輕氫傾卿清擎晴氰情頃請慶瓊窮秋丘邱球求囚酋泅趨區蛆曲軀屈驅渠取娶齲趣去圈顴權醛泉全痊拳犬券勸缺炔瘸卻鵲榷確雀裙群然燃冉染瓤壤攘嚷讓饒擾繞惹熱壬仁人忍韌任認刃妊紉扔仍日戎茸蓉榮融熔溶容絨冗揉柔肉茹蠕儒孺如辱乳汝入褥軟阮蕊瑞銳閏潤若弱撒灑薩腮鰓塞賽三叁傘散桑嗓喪搔騷掃嫂瑟色澀森僧莎砂殺剎沙紗傻啥煞篩曬珊苫杉山刪煽衫閃陜擅贍膳善汕扇繕墑傷商賞晌上尚裳梢捎稍燒芍勺韶少哨邵紹奢賒蛇舌舍赦攝射懾涉社設砷申呻伸身深娠紳神沈審嬸甚腎慎滲聲生甥牲升繩省盛剩勝圣師失獅施濕詩尸虱十石拾時什食蝕實識史矢使屎駛始式示士世柿事拭誓逝勢是嗜噬適仕侍釋飾氏市恃室視試收手首守壽授售受瘦獸蔬樞梳殊抒輸叔舒淑疏書贖孰熟薯暑曙署蜀黍鼠屬術述樹束戍豎墅庶數漱恕刷耍摔衰甩帥栓拴霜雙爽誰水睡稅吮瞬順舜說碩朔爍斯撕嘶思私司絲死肆寺嗣四伺似飼巳松聳慫頌送宋訟誦搜艘擻嗽蘇酥俗素速粟僳塑溯宿訴肅酸蒜算雖隋隨綏髓碎歲穗遂隧祟孫損筍蓑梭唆縮瑣索鎖所塌他它她塔獺撻蹋踏胎苔抬臺泰酞太態汰坍攤貪癱灘壇檀痰潭譚談坦毯袒碳探嘆炭湯塘搪堂棠膛唐糖倘躺淌趟燙掏濤滔絳萄桃逃淘陶討套特藤騰疼謄梯剔踢銻提題蹄啼體替嚏惕涕剃屜天添填田甜恬舔腆挑條迢眺跳貼鐵帖廳聽烴汀廷停亭庭挺艇通桐酮瞳同銅彤童桶捅筒統痛偷投頭透凸禿突圖徒途涂屠土吐兔湍團推頹腿蛻褪退吞屯臀拖托脫鴕陀馱駝橢妥拓唾挖哇蛙洼娃瓦襪歪外豌彎灣玩頑丸烷完碗挽晚皖惋宛婉萬腕汪王亡枉網往旺望忘妄威巍微危韋違桅圍唯惟為濰維葦萎委偉偽尾緯未蔚味畏胃喂魏位渭謂尉慰衛瘟溫蚊文聞紋吻穩紊問嗡翁甕撾蝸渦窩我斡臥握沃巫嗚鎢烏污誣屋無蕪梧吾吳毋武五捂午舞伍侮塢戊霧晤物勿務悟誤昔熙析西硒矽晰嘻吸錫犧稀息希悉膝夕惜熄烯溪汐犀檄襲席習媳喜銑洗系隙戲細瞎蝦匣霞轄暇峽俠狹下廈夏嚇掀锨先仙鮮纖咸賢銜舷閑涎弦嫌顯險現獻縣腺餡羨憲陷限線相廂鑲香箱襄湘鄉翔祥詳想響享項巷橡像向象蕭硝霄削哮囂銷消宵淆曉小孝校肖嘯笑效楔些歇蝎鞋協挾攜邪斜脅諧寫械卸蟹懈泄瀉謝屑薪芯鋅欣辛新忻心信釁星腥猩惺興刑型形邢行醒幸杏性姓兄兇胸匈洶雄熊休修羞朽嗅銹秀袖繡墟戌需虛噓須徐許蓄酗敘旭序畜恤絮婿緒續軒喧宣懸旋玄選癬眩絢靴薛學穴雪血勛熏循旬詢尋馴巡殉汛訓訊遜迅壓押鴉鴨呀丫芽牙蚜崖衙涯雅啞亞訝焉咽閹煙淹鹽嚴研蜒巖延言顏閻炎沿奄掩眼衍演艷堰燕厭硯雁唁彥焰宴諺驗殃央鴦秧楊揚佯瘍羊洋陽氧仰癢養樣漾邀腰妖瑤搖堯遙窯謠姚咬舀藥要耀椰噎耶爺野冶也頁掖業葉曳腋夜液一壹醫揖銥依伊衣頤夷遺移儀胰疑沂宜姨彝椅蟻倚已乙矣以藝抑易邑屹億役臆逸肄疫亦裔意毅憶義益溢詣議誼譯異翼翌繹茵蔭因殷音陰姻吟銀淫寅飲尹引隱印英櫻嬰鷹應纓瑩螢營熒蠅迎贏盈影穎硬映喲擁傭臃癰庸雍踴蛹詠泳涌永恿勇用幽優悠憂尤由郵鈾猶油游酉有友右佑釉誘又幼迂淤于盂榆虞愚輿余俞逾魚愉渝漁隅予娛雨與嶼禹宇語羽玉域芋郁吁遇喻峪御愈欲獄育譽浴寓裕預豫馭鴛淵冤元垣袁原援轅園員圓猿源緣遠苑愿怨院曰約越躍鑰岳粵月悅閱耘云鄖勻隕允運蘊醞暈韻孕匝砸雜栽哉災宰載再在咱攢暫贊贓臟葬遭糟鑿藻棗早澡蚤躁噪造皂灶燥責擇則澤賊怎增憎曾贈扎喳渣札軋鍘閘眨柵榨咋乍炸詐摘齋宅窄債寨瞻氈詹粘沾盞斬輾嶄展蘸棧占戰站湛綻樟章彰漳張掌漲杖丈帳賬仗脹瘴障招昭找沼趙照罩兆肇召遮折哲蟄轍者鍺蔗這浙珍斟真甄砧臻貞針偵枕疹診震振鎮陣蒸掙睜征猙爭怔整拯正政幀癥鄭證芝枝支吱蜘知肢脂汁之織職直植殖執值侄址指止趾只旨紙志摯擲至致置幟峙制智秩稚質炙痔滯治窒中盅忠鐘衷終種腫重仲眾舟周州洲謅粥軸肘帚咒皺宙晝驟珠株蛛朱豬諸誅逐竹燭煮拄矚囑主著柱助蛀貯鑄筑住注祝駐抓爪拽專磚轉撰賺篆樁莊裝妝撞壯狀椎錐追贅墜綴諄準捉拙卓桌琢茁酌啄著灼濁茲咨資姿滋淄孜紫仔籽滓子自漬字鬃棕蹤宗綜總縱鄒走奏揍租足卒族祖詛阻組鉆纂嘴醉最罪尊遵昨左佐柞做作坐座'
? ? ? ,@fall=N'啊阿埃挨哎唉哀皚癌藹矮艾礙愛隘鞍氨安俺按暗岸胺案骯昂盎凹敖熬翺襖傲奧懊澳芭捌扒叭吧笆八疤巴拔跋靶把耙壩霸罷爸白柏百擺佰敗拜稗斑班搬扳般頒板版扮拌伴瓣半辦絆邦幫梆榜膀綁棒磅蚌鎊傍謗苞胞包褒剝薄雹保堡飽寶抱報暴豹鮑爆杯碑悲卑北輩背貝鋇倍狽備憊焙被奔苯本笨崩繃甭泵蹦迸逼鼻比鄙筆彼碧蓖蔽畢斃毖幣庇痹閉敝弊必辟壁臂避陛鞭邊編貶扁便變卞辨辯辮遍標彪膘表鼈憋別癟彬斌瀕濱賓擯兵冰柄丙秉餅炳病並玻菠播撥缽波博勃搏鉑箔伯帛舶脖膊渤泊駁捕蔔哺補埠不布步簿部怖擦猜裁材才財睬踩采彩菜蔡餐參蠶殘慚慘燦蒼艙倉滄藏操糙槽曹草廁策側冊測層蹭插叉茬茶查碴搽察岔差詫拆柴豺攙摻蟬饞讒纏鏟産闡顫昌猖場嘗常長償腸廠敞暢唱倡超抄鈔朝嘲潮巢吵炒車扯撤掣徹澈郴臣辰塵晨忱沈陳趁襯撐稱城橙成呈乘程懲澄誠承逞騁秤吃癡持匙池遲弛馳恥齒侈尺赤翅斥熾充沖蟲崇寵抽酬疇躊稠愁籌仇綢瞅醜臭初出櫥廚躇鋤雛滁除楚礎儲矗搐觸處揣川穿椽傳船喘串瘡窗幢床闖創吹炊捶錘垂春椿醇唇淳純蠢戳綽疵茨磁雌辭慈瓷詞此刺賜次聰蔥囪匆從叢湊粗醋簇促躥篡竄摧崔催脆瘁粹淬翠村存寸磋撮搓措挫錯搭達答瘩打大呆歹傣戴帶殆代貸袋待逮怠耽擔丹單鄲撣膽旦氮但憚淡誕彈蛋當擋黨蕩檔刀搗蹈倒島禱導到稻悼道盜德得的蹬燈登等瞪凳鄧堤低滴迪敵笛狄滌翟嫡抵底地蒂第帝弟遞締顛掂滇碘點典靛墊電佃甸店惦奠澱殿碉叼雕凋刁掉吊釣調跌爹碟蝶疊諜疊丁盯叮釘頂鼎錠定訂丟東冬董懂動棟侗恫凍洞兜抖鬥陡豆逗痘都督毒犢獨讀堵睹賭杜鍍肚度渡妒端短鍛段斷緞堆兌隊對墩噸蹲敦頓囤鈍盾遁掇哆多奪垛躲朵跺舵剁惰墮蛾峨鵝俄額訛娥惡厄扼遏鄂餓恩而兒耳爾餌洱二貳發罰筏伐乏閥法琺藩帆番翻樊礬釩繁凡煩反返範販犯飯泛坊芳方肪房防妨仿訪紡放菲非啡飛肥匪誹吠肺廢沸費芬酚吩氛分紛墳焚汾粉奮份忿憤糞豐封楓蜂峰鋒風瘋烽逢馮縫諷奉鳳佛否夫敷膚孵扶拂輻幅氟符伏俘服浮涪福袱弗甫撫輔俯釜斧脯腑府腐赴副覆賦複傅付阜父腹負富訃附婦縛咐噶嘎該改概鈣蓋溉幹甘桿柑竿肝趕感稈敢贛岡剛鋼缸肛綱崗港杠篙臯高膏羔糕搞鎬稿告哥歌擱戈鴿胳疙割革葛格蛤閣隔鉻個各給根跟耕更庚羹埂耿梗工攻功恭龔供躬公宮弓鞏汞拱貢共鈎勾溝茍狗垢構購夠辜菇咕箍估沽孤姑鼓古蠱骨谷股故顧固雇刮瓜剮寡掛褂乖拐怪棺關官冠觀管館罐慣灌貫光廣逛瑰規圭矽歸龜閨軌鬼詭癸桂櫃跪貴劊輥滾棍鍋郭國果裹過哈骸孩海氦亥害駭酣憨邯韓含涵寒函喊罕翰撼捍旱憾悍焊汗漢夯杭航壕嚎豪毫郝好耗號浩呵喝荷菏核禾和何合盒貉閡河涸赫褐鶴賀嘿黑痕很狠恨哼亨橫衡恒轟哄烘虹鴻洪宏弘紅喉侯猴吼厚候後呼乎忽瑚壺葫胡蝴狐糊湖弧虎唬護互滬戶花嘩華猾滑畫劃化話槐徊懷淮壞歡環桓還緩換患喚瘓豢煥渙宦幻荒慌黃磺蝗簧皇凰惶煌晃幌恍謊灰揮輝徽恢蛔回毀悔慧卉惠晦賄穢會燴彙諱誨繪葷昏婚魂渾混豁活夥火獲或惑霍貨禍擊圾基機畸稽積箕肌饑跡激譏雞姬績緝吉極棘輯籍集及急疾汲即嫉級擠幾脊己薊技冀季伎祭劑悸濟寄寂計記既忌際妓繼紀嘉枷夾佳家加莢頰賈甲鉀假稼價架駕嫁殲監堅尖箋間煎兼肩艱奸緘繭檢柬堿鹼揀撿簡儉剪減薦檻鑒踐賤見鍵箭件健艦劍餞漸濺澗建僵姜將漿江疆蔣槳獎講匠醬降蕉椒礁焦膠交郊澆驕嬌嚼攪鉸矯僥腳狡角餃繳絞剿教酵轎較叫窖揭接皆稭街階截劫節莖睛晶鯨京驚精粳經井警景頸靜境敬鏡徑痙靖竟競淨炯窘揪究糾玖韭久灸九酒廄救舊臼舅咎就疚鞠拘狙疽居駒菊局咀矩舉沮聚拒據巨具距踞鋸俱句懼炬劇捐鵑娟倦眷卷絹撅攫抉掘倔爵桔傑捷睫竭潔結解姐戒藉芥界借介疥誡屆巾筋斤金今津襟緊錦僅謹進靳晉禁近燼浸盡勁荊兢覺決訣絕均菌鈞軍君峻俊竣?？をE喀咖卡咯開揩楷凱慨刊堪勘坎砍看康慷糠扛抗亢炕考拷烤靠坷苛柯棵磕顆科殼咳可渴克刻客課肯啃墾懇坑吭空恐孔控摳口扣寇枯哭窟苦酷庫褲誇垮挎跨胯塊筷儈快寬款匡筐狂框礦眶曠況虧盔巋窺葵奎魁傀饋愧潰坤昆捆困括擴廓闊垃拉喇蠟臘辣啦萊來賴藍婪欄攔籃闌蘭瀾讕攬覽懶纜爛濫瑯榔狼廊郎朗浪撈勞牢老佬姥酪烙澇勒樂雷鐳蕾磊累儡壘擂肋類淚棱楞冷厘梨犁黎籬貍離漓理李裏鯉禮莉荔吏栗麗厲勵礫曆利傈例俐痢立粒瀝隸力璃哩倆聯蓮連鐮廉憐漣簾斂臉鏈戀煉練糧涼梁粱良兩輛量晾亮諒撩聊僚療燎寥遼潦了撂鐐廖料列裂烈劣獵琳林磷霖臨鄰鱗淋凜賃吝拎玲菱零齡鈴伶羚淩靈陵嶺領另令溜琉榴硫餾留劉瘤流柳六龍聾嚨籠窿隆壟攏隴樓婁摟簍漏陋蘆盧顱廬爐擄鹵虜魯麓碌露路賂鹿潞祿錄陸戮驢呂鋁侶旅履屢縷慮氯律率濾綠巒攣孿灤卵亂掠略掄輪倫侖淪綸論蘿螺羅邏鑼籮騾裸落洛駱絡媽麻瑪碼螞馬罵嘛嗎埋買麥賣邁脈瞞饅蠻滿蔓曼慢漫謾芒茫盲氓忙莽貓茅錨毛矛鉚卯茂冒帽貌貿麼玫枚梅酶黴煤沒眉媒鎂每美昧寐妹媚門悶們萌蒙檬盟錳猛夢孟瞇醚靡糜迷謎彌米秘覓泌蜜密冪棉眠綿冕免勉娩緬面苗描瞄藐秒渺廟妙蔑滅民抿皿敏憫閩明螟鳴銘名命謬摸摹蘑模膜磨摩魔抹末莫墨默沫漠寞陌謀牟某拇牡畝姆母墓暮幕募慕木目睦牧穆拿哪吶鈉那娜納氖乃奶耐奈南男難囊撓腦惱鬧淖呢餒內嫩能妮霓倪泥尼擬你匿膩逆溺蔫拈年碾攆撚念娘釀鳥尿捏聶孽齧鑷鎳涅您檸獰凝甯擰濘牛扭鈕紐膿濃農弄奴努怒女暖虐瘧挪懦糯諾哦歐鷗毆藕嘔偶漚啪趴爬帕怕琶拍排牌徘湃派攀潘盤磐盼畔判叛乓龐旁耪胖拋咆刨炮袍跑泡呸胚培裴賠陪配佩沛噴盆砰抨烹澎彭蓬棚硼篷膨朋鵬捧碰坯砒霹批披劈琵毗啤脾疲皮匹痞僻屁譬篇偏片騙飄漂瓢票撇瞥拼頻貧品聘乒坪蘋萍平憑瓶評屏坡潑頗婆破魄迫粕剖撲鋪仆莆葡菩蒲埔樸圃普浦譜曝瀑期欺棲戚妻七淒漆柒沏其棋奇歧畦崎臍齊旗祈祁騎起豈乞企啓契砌器氣迄棄汽泣訖掐洽牽扡釺鉛千遷簽仟謙乾黔錢鉗前潛遣淺譴塹嵌欠歉槍嗆腔羌牆薔強搶橇鍬敲悄橋瞧喬僑巧鞘撬翹峭俏竅切茄且怯竊欽侵親秦琴勤芹擒禽寢沁青輕氫傾卿清擎晴氰情頃請慶瓊窮秋丘邱球求囚酋泅趨區蛆曲軀屈驅渠取娶齲趣去圈顴權醛泉全痊拳犬券勸缺炔瘸卻鵲榷確雀裙群然燃冉染瓤壤攘嚷讓饒擾繞惹熱壬仁人忍韌任認刃妊紉扔仍日戎茸蓉榮融熔溶容絨冗揉柔肉茹蠕儒孺如辱乳汝入褥軟阮蕊瑞銳閏潤若弱撒灑薩腮鰓塞賽三三傘散桑嗓喪搔騷掃嫂瑟色澀森僧莎砂殺剎沙紗傻啥煞篩曬珊苫杉山刪煽衫閃陝擅贍膳善汕扇繕墑傷商賞晌上尚裳梢捎稍燒芍勺韶少哨邵紹奢賒蛇舌舍赦攝射懾涉社設砷申呻伸身深娠紳神沈審嬸甚腎慎滲聲生甥牲升繩省盛剩勝聖師失獅施濕詩屍虱十石拾時什食蝕實識史矢使屎駛始式示士世柿事拭誓逝勢是嗜噬適仕侍釋飾氏市恃室視試收手首守壽授售受瘦獸蔬樞梳殊抒輸叔舒淑疏書贖孰熟薯暑曙署蜀黍鼠屬術述樹束戍豎墅庶數漱恕刷耍摔衰甩帥栓拴霜雙爽誰水睡稅吮瞬順舜說碩朔爍斯撕嘶思私司絲死肆寺嗣四伺似飼巳松聳慫頌送宋訟誦搜艘擻嗽蘇酥俗素速粟僳塑溯宿訴肅酸蒜算雖隋隨綏髓碎歲穗遂隧祟孫損筍蓑梭唆縮瑣索鎖所塌他它她塔獺撻蹋踏胎苔擡臺泰酞太態汰坍攤貪癱灘壇檀痰潭譚談坦毯袒碳探歎炭湯塘搪堂棠膛唐糖倘躺淌趟燙掏濤滔縧萄桃逃淘陶討套特藤騰疼謄梯剔踢銻提題蹄啼體替嚏惕涕剃屜天添填田甜恬舔腆挑條迢眺跳貼鐵帖廳聽烴汀廷停亭庭挺艇通桐酮瞳同銅彤童桶捅筒統痛偷投頭透凸禿突圖徒途塗屠土吐兔湍團推頹腿蛻褪退吞屯臀拖托脫鴕陀馱駝橢妥拓唾挖哇蛙窪娃瓦襪歪外豌彎灣玩頑丸烷完碗挽晚皖惋宛婉萬腕汪王亡枉網往旺望忘妄威巍微危韋違桅圍唯惟爲濰維葦萎委偉僞尾緯未蔚味畏胃喂魏位渭謂尉慰衛瘟溫蚊文聞紋吻穩紊問嗡翁甕撾蝸渦窩我斡臥握沃巫嗚鎢烏汙誣屋無蕪梧吾吳毋武五捂午舞伍侮塢戊霧晤物勿務悟誤昔熙析西硒矽晰嘻吸錫犧稀息希悉膝夕惜熄烯溪汐犀檄襲席習媳喜銑洗系隙戲細瞎蝦匣霞轄暇峽俠狹下廈夏嚇掀鍁先仙鮮纖鹹賢銜舷閑涎弦嫌顯險現獻縣腺餡羨憲陷限線相廂鑲香箱襄湘鄉翔祥詳想響享項巷橡像向象蕭硝霄削哮囂銷消宵淆曉小孝校肖嘯笑效楔些歇蠍鞋協挾攜邪斜脅諧寫械卸蟹懈泄瀉謝屑薪芯鋅欣辛新忻心信釁星腥猩惺興刑型形邢行醒幸杏性姓兄兇胸匈洶雄熊休修羞朽嗅鏽秀袖繡墟戌需虛噓須徐許蓄酗敘旭序畜恤絮婿緒續軒喧宣懸旋玄選癬眩絢靴薛學穴雪血勳熏循旬詢尋馴巡殉汛訓訊遜迅壓押鴉鴨呀丫芽牙蚜崖衙涯雅啞亞訝焉咽閹煙淹鹽嚴研蜒巖延言顔閻炎沿奄掩眼衍演豔堰燕厭硯雁唁彥焰宴諺驗殃央鴦秧楊揚佯瘍羊洋陽氧仰癢養樣漾邀腰妖瑤搖堯遙窯謠姚咬舀藥要耀椰噎耶爺野冶也頁掖業葉曳腋夜液一壹醫揖銥依伊衣頤夷遺移儀胰疑沂宜姨彜椅蟻倚已乙矣以藝抑易邑屹億役臆逸肄疫亦裔意毅憶義益溢詣議誼譯異翼翌繹茵蔭因殷音陰姻吟銀淫寅飲尹引隱印英櫻嬰鷹應纓瑩螢營熒蠅迎贏盈影穎硬映喲擁傭臃癰庸雍踴蛹詠泳湧永恿勇用幽優悠憂尤由郵鈾猶油遊酉有友右佑釉誘又幼迂淤于盂榆虞愚輿余俞逾魚愉渝漁隅予娛雨與嶼禹宇語羽玉域芋郁籲遇喻峪禦愈欲獄育譽浴寓裕預豫馭鴛淵冤元垣袁原援轅園員圓猿源緣遠苑願怨院曰約越躍鑰嶽粵月悅閱耘雲鄖勻隕允運蘊醞暈韻孕匝砸雜栽哉災宰載再在咱攢暫贊贓髒葬遭糟鑿藻棗早澡蚤躁噪造皂竈燥責擇則澤賊怎增憎曾贈紮喳渣劄軋鍘閘眨柵榨咋乍炸詐摘齋宅窄債寨瞻氈詹粘沾盞斬輾嶄展蘸棧占戰站湛綻樟章彰漳張掌漲杖丈帳賬仗脹瘴障招昭找沼趙照罩兆肇召遮折哲蟄轍者鍺蔗這浙珍斟真甄砧臻貞針偵枕疹診震振鎮陣蒸掙睜征猙爭怔整拯正政幀癥鄭證芝枝支吱蜘知肢脂汁之織職直植殖執值侄址指止趾只旨紙志摯擲至致置幟峙制智秩稚質炙痔滯治窒中盅忠鍾衷終種腫重仲衆舟周州洲謅粥軸肘帚咒皺宙晝驟珠株蛛朱豬諸誅逐竹燭煮拄矚囑主著柱助蛀貯鑄築住注祝駐抓爪拽專磚轉撰賺篆樁莊裝妝撞壯狀椎錐追贅墜綴諄準捉拙卓桌琢茁酌啄著灼濁茲咨資姿滋淄孜紫仔籽滓子自漬字鬃棕蹤宗綜總縱鄒走奏揍租足卒族祖詛阻組鑽纂嘴醉最罪尊遵昨左佐柞做作坐座'
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 begindeclare?@r?varchar(300)set?@r=''while?len(@A1)?>0beginset?@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,'')endreturn?@r end--測試示例 select?dbo.fn_and('000111011001','010011010011')? --運行結果 /* */--問題：有表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 begindeclare?@r?intset?@r=0select?@r=@r?|?val?from?tab?where?id=@idreturn?@r endgo --測試示例 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.aspx32、求某段時間內星期幾的天數
--原帖地址：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 Begindeclare@sResult?nvarchar(100),????--結果描述@nResult?int,??????????????--結果天數@nWeekValue?int,???????????--起始日期是星期幾(1-周日、-周一...7-周六)@nDays?int,????????????????--時間段內的天數@nBeforeDays?int,??????????--起始日期到第一次相符日期的天數@sWeekName?varchar(20),????--所求日期的星期名稱@tmpDate?smalldatetimeset?@WeekValue=@WeekValue?%?7if?@WeekValue=0set?@WeekValue=7--如果起始日期大于終止日期，則對換if?@DateS>@DateEselect?@tmpDate=@DateE,@DateE=@DateS,@DateS=@tmpDate--取得起始日期的星期數，與設置無關(1-周日、-周一...7-周六)select?@nWeekValue=(Datepart(dw,@DateS)+(@@Datefirst?%7))%7if?@nWeekValue=0set?@nWeekValue=7set?@sWeekName=DateName(dw,DateAdd(d,@WeekValue-@nWeekValue,@DateS))if?@WeekValue>=@nWeekValueset?@nBeforeDays=@WeekValue-@nWeekValueelseset?@nBeforeDays=7-abs(@WeekValue-@nWeekValue)--取得時間段內的天數select?@nDays=Datediff(d,@DateS,@DateE)+1if?@nDays<@nBeforeDaysselect?@nResult=0elseselect?@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 begindeclare?@Result?decimal(18,6)set?@Value=isnull(@Value,0)if?@RoundMode='1'?????????--舍去Select?@Result=Round(@Value,@Bit,1)else?if?@RoundMode='2'????--進位Beginif?@Value>Round(@Value,@Bit,1)????--確保在有尾數的情況下才進位Select?@Result=Round(@Value,@Bit,1)+1.0/Power(10,@Bit)elseSelect?@Result=Round(@Value,@Bit,1)endelseSelect?@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 */轉載于:https://blog.51cto.com/rmlifejun/1828648
總結
以上是生活随笔為你收集整理的Microsoft SQL Server 自定义函数整理大全的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： [Win10应用开发] 使用 Windo
- 下一篇： Content Security Pol