--创建函数

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'靅' 

    u