SQL Server 函数汉字转首字母或全拼

/*方法一函数名称:GetPY实现功能:将一串汉字输入返回每个汉字的拼音首字母如输入-->'凡九龙'-->输出-->fjl.完成时间:-09-18作者:凡九龙参数:@str-->是你想得到拼音首字母的汉字返回值:汉字的拼音首字母*/create function fun_getPY(@str nvarchar(4000))returns nvarchar(4000)asbegindeclare @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 PY from (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 @word end)set @str=right(@str,len(@str)-1)endreturn @PYend--函数调用实例：--select dbo.fun_getPY('中华人民共和国')-------------------------------------------------------------------可支持大字符集20000个汉字! create function f_ch2py(@chn nchar(1)) returns char(1) as begin declare @n int declare @c char(1) set @n = 63 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 '丌' 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 @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b return(@c) end go select dbo.f_ch2py('中') --Z select dbo.f_ch2py('国') --G select dbo.f_ch2py('人') --R select dbo.f_ch2py('镆') --M go -----------------调用 CREATE FUNCTION F_GetHelpCode ( @cName VARCHAR(20) ) RETURNS VARCHAR(12) AS BEGIN DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(12), @e VARCHAR(12), @iAscii SMALLINT SELECT @i=1, @L=0 , @cHelpCode='' while @L<=12 AND @i<=LEN(@cName) BEGIN SELECT @e=LOWER(SUBSTRING(@cname,@i,1)) SELECT @iAscii=ASCII(@e) IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95 SELECT @cHelpCode=@cHelpCode +@e ELSE IF @iAscii>=176 AND @iAscii <=247 SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e) ELSE SELECT @L=@L-1 SELECT @i=@i+1, @L=@L+1 END RETURN @cHelpCode END GO --调用 select dbo.F_GetHelpCode('大力')------------------------------------------------/*方法二函数名称:GetPY实现功能:将一串汉字输入返回每个汉字的拼音首字母如输入-->'凡九龙'-->输出-->fjl.完成时间:-09-18作者:凡九龙参数:@str-->是你想得到拼音首字母的汉字返回值:汉字的拼音首字母*/create function GetPY(@str varchar(500))returns varchar(500)asbegindeclare @cyc int,@length int,@str1 varchar(100),@charcate varbinary(20)set @cyc=1--从第几个字开始取set @length=len(@str)--输入汉字的长度set @str1=''--用于存放返回值while @cyc<=@lengthbegin select @charcate=cast(substring(@str,@cyc,1) as varbinary)--每次取出一个字并将其转变成二进制,便于与GBK编码表进行比较if @charcate>=0XB0A1 and @charcate<=0XB0C4set @str1=@str1+'A'--说明此汉字的首字母为A,以下同上else if @charcate>=0XB0C5 and @charcate<=0XB2C0set @str1=@str1+'B'else if @charcate>=0XB2C1 and @charcate<=0XB4EDset @str1=@str1+'C'else if @charcate>=0XB4EE and @charcate<=0XB6E9set @str1=@str1+'D'else if @charcate>=0XB6EA and @charcate<=0XB7A1set @str1=@str1+'E'else if @charcate>=0XB7A2 and @charcate<=0XB8C0set @str1=@str1+'F'else if @charcate>=0XB8C1 and @charcate<=0XB9FDset @str1=@str1+'G'else if @charcate>=0XB9FE and @charcate<=0XBBF6set @str1=@str1+'H'else if @charcate>=0XBBF7 and @charcate<=0XBFA5set @str1=@str1+'J'else if @charcate>=0XBFA6 and @charcate<=0XC0ABset @str1=@str1+'K'else if @charcate>=0XC0AC and @charcate<=0XC2E7set @str1=@str1+'L'else if @charcate>=0XC2E8 and @charcate<=0XC4C2set @str1=@str1+'M'else if @charcate>=0XC4C3 and @charcate<=0XC5B5set @str1=@str1+'N'else if @charcate>=0XC5B6 and @charcate<=0XC5BDset @str1=@str1+'O'else if @charcate>=0XC5BE and @charcate<=0XC6D9set @str1=@str1+'P'else if @charcate>=0XC6DA and @charcate<=0XC8BAset @str1=@str1+'Q'else if @charcate>=0XC8BB and @charcate<=0XC8F5set @str1=@str1+'R'else if @charcate>=0XC8F6 and @charcate<=0XCBF9set @str1=@str1+'S'else if @charcate>=0XCBFA and @charcate<=0XCDD9set @str1=@str1+'T'else if @charcate>=0XCDDA and @charcate<=0XCEF3set @str1=@str1+'W'else if @charcate>=0XCEF4 and @charcate<=0XD1B8set @str1=@str1+'X'else if @charcate>=0XD1B9 and @charcate<=0XD4D0set @str1=@str1+'Y'else if @charcate>=0XD4D1 and @charcate<=0XD7F9set @str1=@str1+'Z'set @cyc=@cyc+1--取出输入汉字的下一个字endreturn @str1--返回输入汉字的首字母end--测试数据--select dbo.GetPY('我就是这么流弊')------------------------------------------------------------------/*函数名称: procGetPinYin实现功能:将一串汉字输入返回每个汉字的全拼如输入-->'凡九龙'-->输出-->fanjiulong.完成时间:-09-18作者:凡九龙参数:@str-->是你想得到全拼的汉字返回值:汉字的全拼*/create function [dbo].procGetPinYin(@str varchar(100)) returns varchar(8000) as begin declare @re varchar(8000),@crs varchar(10) declare @strlen int select @strlen=len(@str),@re='' while @strlen>0 begin set @crs= substring(@str,@strlen,1) select @re= case when @crs<'吖' then @crs when @crs<='厑' then 'a' when @crs<='靉' then 'ai' when @crs<='黯' then 'an' when @crs<='醠' then 'ang' when @crs<='驁' then 'ao' when @crs<='欛' then 'ba' when @crs<='瓸' then 'bai' when @crs<='瓣' then 'ban' when @crs<='鎊' then 'bang' when @crs<='鑤' then 'bao' when @crs<='鐾' then 'bei' when @crs<='輽' then 'ben' when @crs<='鏰' then 'beng' when @crs<='鼊' then 'bi' when @crs<='變' then 'bian' when @crs<='鰾' then 'biao' when @crs<='彆' then 'bie' when @crs<='鬢' then 'bin' when @crs<='靐' then 'bing' when @crs<='蔔' then 'bo' when @crs<='簿' then 'bu' when @crs<='囃' then 'ca' when @crs<='乲' then 'cai' when @crs<='爘' then 'can' when @crs<='賶' then 'cang' when @crs<='鼜' then 'cao' when @crs<='簎' then 'ce' when @crs<='笒' then 'cen' when @crs<='乽' then 'ceng' when @crs<='詫' then 'cha' when @crs<='囆' then 'chai' when @crs<='顫' then 'chan' when @crs<='韔' then 'chang' when @crs<='觘' then 'chao' when @crs<='爡' then 'che' when @crs<='讖' then 'chen' when @crs<='秤' then 'cheng' when @crs<='鷘' then 'chi' when @crs<='銃' then 'chong' when @crs<='殠' then 'chou' when @crs<='矗' then 'chu' when @crs<='踹' then 'chuai' when @crs<='鶨' then 'chuan' when @crs<='愴' then 'chuang' when @crs<='顀' then 'chui' when @crs<='蠢' then 'chun' when @crs<='縒' then 'chuo' when @crs<='嗭' then 'ci' when @crs<='謥' then 'cong' when @crs<='輳' then 'cou' when @crs<='顣' then 'cu' when @crs<='爨' then 'cuan' when @crs<='臎' then 'cui' when @crs<='籿' then 'cun' when @crs<='錯' then 'cuo' when @crs<='橽' then 'da' when @crs<='靆' then 'dai' when @crs<='饏' then 'dan' when @crs<='闣' then 'dang' when @crs<='纛' then 'dao' when @crs<='的' then 'de' when @crs<='扽' then 'den' when @crs<='鐙' then 'deng' when @crs<='螮' then 'di' when @crs<='嗲' then 'dia' when @crs<='驔' then 'dian' when @crs<='鑃' then 'diao' when @crs<='嚸' then 'die' when @crs<='顁' then 'ding' when @crs<='銩' then 'diu' when @crs<='霘' then 'dong' when @crs<='鬭' then 'dou' when @crs<='蠹' then 'du' when @crs<='叾' then 'duan' when @crs<='譵' then 'dui' when @crs<='踲' then 'dun' when @crs<='鵽' then 'duo' when @crs<='鱷' then 'e' when @crs<='摁' then 'en' when @crs<='鞥' then 'eng' when @crs<='樲' then 'er' when @crs<='髮' then 'fa' when @crs<='瀪' then 'fan' when @crs<='放' then 'fang' when @crs<='靅' then 'fei' when @crs<='鱝' then 'fen' when @crs<='覅' then 'feng' when @crs<='梻' then 'fo' when @crs<='鴀' then 'fou' when @crs<='猤' then 'fu' when @crs<='魀' then 'ga' when @crs<='瓂' then 'gai' when @crs<='灨' then 'gan' when @crs<='戇' then 'gang' when @crs<='鋯' then 'gao' when @crs<='獦' then 'ge' when @crs<='給' then 'gei' when @crs<='搄' then 'gen' when @crs<='堩' then 'geng' when @crs<='兣' then 'gong' when @crs<='購' then 'gou' when @crs<='顧' then 'gu' when @crs<='詿' then 'gua' when @crs<='恠' then 'guai' when @crs<='鱹' then 'guan' when @crs<='撗' then 'guang' when @crs<='鱥' then 'gui' when @crs<='謴' then 'gun' when @crs<='腂' then 'guo' when @crs<='哈' then 'ha' when @crs<='饚' then 'hai' when @crs<='鶾' then 'han' when @crs<='沆' then 'hang' when @crs<='兞' then 'hao' when @crs<='靏' then 'he' when @crs<='嬒' then 'hei' when @crs<='恨' then 'hen' when @crs<='堼' then 'heng' when @crs<='鬨' then 'hong' when @crs<='鱟' then 'hou' when @crs<='鸌' then 'hu' when @crs<='蘳' then 'hua' when @crs<='蘾' then 'huai' when @crs<='鰀' then 'huan' when @crs<='鎤' then 'huang' when @crs<='顪' then 'hui' when @crs<='諢' then 'hun' when @crs<='夻' then 'huo' when @crs<='驥' then 'ji' when @crs<='嗧' then 'jia' when @crs<='鑳' then 'jian' when @crs<='謽' then 'jiang' when @crs<='釂' then 'jiao' when @crs<='繲' then 'jie' when @crs<='齽' then 'jin' when @crs<='竸' then 'jing' when @crs<='蘔' then 'jiong' when @crs<='欍' then 'jiu' when @crs<='爠' then 'ju' when @crs<='羂' then 'juan' when @crs<='钁' then 'jue' when @crs<='攈' then 'jun' when @crs<='鉲' then 'ka' when @crs<='乫' then 'kai' when @crs<='矙' then 'kan' when @crs<='閌' then 'kang' when @crs<='鯌' then 'kao' when @crs<='騍' then 'ke' when @crs<='褃' then 'ken' when @crs<='鏗' then 'keng' when @crs<='廤' then 'kong' when @crs<='鷇' then 'kou' when @crs<='嚳' then 'ku' when @crs<='骻' then 'kua' when @crs<='鱠' then 'kuai' when @crs<='窾' then 'kuan' when @crs<='鑛' then 'kuang' when @crs<='鑎' then 'kui' when @crs<='睏' then 'kun' when @crs<='穒' then 'kuo' when @crs<='鞡' then 'la' when @crs<='籟' then 'lai' when @crs<='糷' then 'lan' when @crs<='唥' then 'lang' when @crs<='軂' then 'lao' when @crs<='餎' then 'le' when @crs<='脷' then 'lei' when @crs<='睖' then 'leng' when @crs<='瓈' then 'li' when @crs<='倆' then 'lia' when @crs<='纞' then 'lian' when @crs<='鍄' then 'liang' when @crs<='瞭' then 'liao' when @crs<='鱲' then 'lie' when @crs<='轥' then 'lin' when @crs<='炩' then 'ling' when @crs<='咯' then 'liu' when @crs<='贚' then 'long' when @crs<='鏤' then 'lou' when @crs<='氇' then 'lu' when @crs<='鑢' then 'lv' when @crs<='亂' then 'luan' when @crs<='擽' then 'lue' when @crs<='論' then 'lun' when @crs<='鱳' then 'luo' when @crs<='嘛' then 'ma' when @crs<='霢' then 'mai' when @crs<='蘰' then 'man' when @crs<='蠎' then 'mang' when @crs<='唜' then 'mao' when @crs<='癦' then 'me' when @crs<='嚜' then 'mei' when @crs<='們' then 'men' when @crs<='霥' then 'meng' when @crs<='羃' then 'mi' when @crs<='麵' then 'mian' when @crs<='廟' then 'miao' when @crs<='鱴' then 'mie' when @crs<='鰵' then 'min' when @crs<='詺' then 'ming' when @crs<='謬' then 'miu' when @crs<='耱' then 'mo' when @crs<='麰' then 'mou' when @crs<='旀' then 'mu' when @crs<='魶' then 'na' when @crs<='錼' then 'nai' when @crs<='婻' then 'nan' when @crs<='齉' then 'nang' when @crs<='臑' then 'nao' when @crs<='呢' then 'ne' when @crs<='焾' then 'nei' when @crs<='嫩' then 'nen' when @crs<='能' then 'neng' when @crs<='嬺' then 'ni' when @crs<='艌' then 'nian' when @crs<='釀' then 'niang' when @crs<='脲' then 'niao' when @crs<='钀' then 'nie' when @crs<='拰' then 'nin' when @crs<='濘' then 'ning' when @crs<='靵' then 'niu' when @crs<='齈' then 'nong' when @crs<='譳' then 'nou' when @crs<='搙' then 'nu' when @crs<='衄' then 'nv' when @crs<='瘧' then 'nue' when @crs<='燶' then 'nuan' when @crs<='桛' then 'nuo' when @crs<='鞰' then 'o' when @crs<='漚' then 'ou' when @crs<='袙' then 'pa' when @crs<='磗' then 'pai' when @crs<='鑻' then 'pan' when @crs<='胖' then 'pang' when @crs<='礮' then 'pao' when @crs<='轡' then 'pei' when @crs<='喯' then 'pen' when @crs<='喸' then 'peng' when @crs<='鸊' then 'pi' when @crs<='騙' then 'pian' when @crs<='慓' then 'piao' when @crs<='嫳' then 'pie' when @crs<='聘' then 'pin' when @crs<='蘋' then 'ping' when @crs<='魄' then 'po' when @crs<='哛' then 'pou' when @crs<='曝' then 'pu' when @crs<='蟿' then 'qi' when @crs<='髂' then 'qia' when @crs<='縴' then 'qian' when @crs<='瓩' then 'qiang' when @crs<='躈' then 'qiao' when @crs<='籡' then 'qie' when @crs<='藽' then 'qin' when @crs<='櫦' then 'qing' when @crs<='瓗' then 'qiong' when @crs<='糗' then 'qiu' when @crs<='覻' then 'qu' when @crs<='勸' then 'quan' when @crs<='礭' then 'que' when @crs<='囕' then 'qun' when @crs<='橪' then 'ran' when @crs<='讓' then 'rang' when @crs<='繞' then 'rao' when @crs<='熱' then 're' when @crs<='餁' then 'ren' when @crs<='陾' then 'reng' when @crs<='馹' then 'ri' when @crs<='穃' then 'rong' when @crs<='嶿' then 'rou' when @crs<='擩' then 'ru' when @crs<='礝' then 'ruan' when @crs<='壡' then 'rui' when @crs<='橍' then 'run' when @crs<='鶸' then 'ruo' when @crs<='栍' then 'sa' when @crs<='虄' then 'sai' when @crs<='閐' then 'san' when @crs<='喪' then 'sang' when @crs<='髞' then 'sao' when @crs<='飋' then 'se' when @crs<='篸' then 'sen' when @crs<='縇' then 'seng' when @crs<='霎' then 'sha' when @crs<='曬' then 'shai' when @crs<='鱔' then 'shan' when @crs<='緔' then 'shang' when @crs<='潲' then 'shao' when @crs<='欇' then 'she' when @crs<='瘮' then 'shen' when @crs<='賸' then 'sheng' when @crs<='瓧' then 'shi' when @crs<='鏉' then 'shou' when @crs<='虪' then 'shu' when @crs<='誜' then 'shua' when @crs<='卛' then 'shuai' when @crs<='腨' then 'shuan' when @crs<='灀' then 'shuang' when @crs<='睡' then 'shui' when @crs<='鬊' then 'shun' when @crs<='鑠' then 'shuo' when @crs<='乺' then 'si' when @crs<='鎹' then 'song' when @crs<='瘶' then 'sou' when @crs<='鷫' then 'su' when @crs<='算' then 'suan' when @crs<='鐩' then 'sui' when @crs<='潠' then 'sun' when @crs<='蜶' then 'suo' when @crs<='襨' then 'ta' when @crs<='燤' then 'tai' when @crs<='賧' then 'tan' when @crs<='燙' then 'tang' when @crs<='畓' then 'tao' when @crs<='蟘' then 'te' when @crs<='朰' then 'teng' when @crs<='趯' then 'ti' when @crs<='舚' then 'tian' when @crs<='糶' then 'tiao' when @crs<='餮' then 'tie' when @crs<='乭' then 'ting' when @crs<='憅' then 'tong' when @crs<='透' then 'tou' when @crs<='鵵' then 'tu' when @crs<='褖' then 'tuan' when @crs<='駾' then 'tui' when @crs<='坉' then 'tun' when @crs<='籜' then 'tuo' when @crs<='韤' then 'wa' when @crs<='顡' then 'wai' when @crs<='贎' then 'wan' when @crs<='朢' then 'wang' when @crs<='躛' then 'wei' when @crs<='璺' then 'wen' when @crs<='齆' then 'weng' when @crs<='齷' then 'wo' when @crs<='鶩' then 'wu' when @crs<='衋' then 'xi' when @crs<='鏬' then 'xia' when @crs<='鼸' then 'xian' when @crs<='鱌' then 'xiang' when @crs<='斆' then 'xiao' when @crs<='躞' then 'xie' when @crs<='釁' then 'xin' when @crs<='臖' then 'xing' when @crs<='敻' then 'xiong' when @crs<='齅' then 'xiu' when @crs<='蓿' then 'xu' when @crs<='贙' then 'xuan' when @crs<='瀥' then 'xue' when @crs<='鑂' then 'xun' when @crs<='齾' then 'ya' when @crs<='灩' then 'yan' when @crs<='樣' then 'yang' when @crs<='鑰' then 'yao' when @crs<='岃' then 'ye' when @crs<='齸' then 'yi' when @crs<='檼' then 'yin' when @crs<='譍' then 'ying' when @crs<='喲' then 'yo' when @crs<='醟' then 'yong' when @crs<='鼬' then 'you' when @crs<='爩' then 'yu' when @crs<='願' then 'yuan' when @crs<='鸙' then 'yue' when @crs<='韻' then 'yun' when @crs<='雥' then 'za' when @crs<='縡' then 'zai' when @crs<='饡' then 'zan' when @crs<='臟' then 'zang' when @crs<='竈' then 'zao' when @crs<='稄' then 'ze' when @crs<='鱡' then 'zei' when @crs<='囎' then 'zen' when @crs<='贈' then 'zeng' when @crs<='醡' then 'zha' when @crs<='瘵' then 'zhai' when @crs<='驏' then 'zhan' when @crs<='瞕' then 'zhang' when @crs<='羄' then 'zhao' when @crs<='鷓' then 'zhe' when @crs<='黮' then 'zhen' when @crs<='證' then 'zheng' when @crs<='豒' then 'zhi' when @crs<='諥' then 'zhong' when @crs<='驟' then 'zhou' when @crs<='鑄' then 'zhu' when @crs<='爪' then 'zhua' when @crs<='跩' then 'zhuai' when @crs<='籑' then 'zhuan' when @crs<='戅' then 'zhuang' when @crs<='鑆' then 'zhui' when @crs<='稕' then 'zhun' when @crs<='籱' then 'zhuo' when @crs<='漬' then 'zi' when @crs<='縱' then 'zong' when @crs<='媰' then 'zou' when @crs<='謯' then 'zu' when @crs<='攥' then 'zuan' when @crs<='欈' then 'zui' when @crs<='銌' then 'zun' when @crs<='咗' then 'zuo' --else @crs end+' '+@re,@strlen=@strlen-1 --去掉拼音之间的间隔 else @crs end+''+@re,@strlen=@strlen-1 end return(@re) end go --调用方法 select dbo. procGetPinYin ('中國')