sql文字转换全拼_SQL汉字转拼音函数-支持首字母、全拼

作者不详--方法一sqlserver汉字转拼音首字母--调用方法 select dbo.procGetPY ('中國')

Create FUNCTIONdbo.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 1PYFROM(SELECT 'A' AS PY,N'驁' ASWORDUNION 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>=@WORDCOLLATE CHINESE_PRC_CS_AS_KS_WSORDER 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

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'