作者不详
  

--方法一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