SQLSERVER获取汉字拼音码

 

用户的输入经常用到拼音码选择器 

--调用方法 select  dbo.wsh_GetPY('汉字')
ALTER FUNCTION [dbo].[wsh_GetPY] 
( 
@str NVARCHAR(4000) 
) 
RETURNS NVARCHAR(4000) 

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

　　

ALTER function [dbo].wsh_GetPinYin(@str  varchar (100))
returns varchar (8000)
-- sqlserver汉字转全拼
--调用方法 select dbo.wsh_GetPinYin ('汉字')
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