SQL多音词汉字转拼音
遇到一个需求 取字符串的首字母 汉字则取其拼音
之前在网上搜索到了转换函数 但对于多音词的处理未能实现
这儿贴出 来自 CSDN中在
# maco_wang
# (☆叶子☆)
空间找到的函数
求能解决多音词的函数 单个多音字暂时不考虑
--创建函数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'氇'