SQL将汉字转化为拼音（首字母、全拼）

冷不防 2022-08-02 06:18 447阅读 0赞

--方法一sqlserver汉字转拼音首字母
    --调用方法 select dbo.procGetPY ('中國')
    Create FUNCTION dbo.procGetPY ( @str NVARCHAR(4000) ) /* select dbo. procGetPYFirstLetter ('中國') */ RETURNS NVARCHAR(4000) --WITH ENCRYPTION 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

--方法二sqlserver汉字转全拼 --调用方法 select dbo. procGetPinYin ('中國') 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