当前位置:asp编程网>技术教程>Database教程>  正文

SQL Server汉字转为拼音函数

2012-08-30 10:45:08   来源:    作者:佚名   浏览量:622   收藏
转首字母:
create function 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

(鼠标移到代码上去,在代码的顶部会出现四个图标,第一个是查看源代码,第二个是复制代码,第三个是打印代码,第四个是帮助)
测试:
Select dbo.GetPy('中华人民共和国')
(鼠标移到代码上去,在代码的顶部会出现四个图标,第一个是查看源代码,第二个是复制代码,第三个是打印代码,第四个是帮助)

得到结果:ZHRMGHG

得到全拼音的:

Create function [dbo].[f_GetPy](@str varchar(100))
returns varchar(8000)
as
begin
declare @re varchar(8000)
--生成临时表
declare @t table(chr nchar(1) collate Chinese_PRC_CS_AS_KS_WS,py nvarchar(20)) 
insert into @t select'吖','a' 
insert into @t select'厑','aes' 
insert into @t select'哎','ai' 
insert into @t select'安','an' 
insert into @t select'肮','ang' 
insert into @t select'凹','ao' 
insert into @t select'八','ba' 
insert into @t select'挀','bai' 
insert into @t select'兡','baike' 
insert into @t select'瓸','baiwa' 
insert into @t select'扳','ban' 
insert into @t select'邦','bang' 
insert into @t select'勹','bao' 
insert into @t select'萡','be' 
insert into @t select'陂','bei' 
insert into @t select'奔','ben' 
insert into @t select'伻','beng' 
insert into @t select'皀','bi' 
insert into @t select'边','bian' 
insert into @t select'辪','uu' 
insert into @t select'灬','biao' 
insert into @t select'憋','bie' 
insert into @t select'汃','bin' 
insert into @t select'冫','bing' 
insert into @t select'癶','bo' 
insert into @t select'峬','bu' 
insert into @t select'嚓','ca' 
insert into @t select'偲','cai' 
insert into @t select'乲','cal' 
insert into @t select'参','can' 
insert into @t select'仓','cang' 
insert into @t select'撡','cao' 
insert into @t select'冊','ce' 
insert into @t select'膥','cen' 
insert into @t select'噌','ceng' 
insert into @t select'硛','ceok' 
insert into @t select'岾','ceom' 
insert into @t select'猠','ceon' 
insert into @t select'乽','ceor' 
insert into @t select'叉','cha' 
insert into @t select'犲','chai' 
insert into @t select'辿','chan' 
insert into @t select'伥','chang' 
insert into @t select'抄','chao' 
insert into @t select'车','che' 
insert into @t select'抻','chen' 
insert into @t select'阷','cheng' 
insert into @t select'吃','chi' 
insert into @t select'充','chong' 
insert into @t select'抽','chou' 
insert into @t select'出','chu' 
insert into @t select'膗','chuai' 
insert into @t select'巛','chuan' 
insert into @t select'刅','chuang' 
insert into @t select'吹','chui' 
insert into @t select'旾','chun' 
insert into @t select'踔','chuo' 
insert into @t select'呲','ci' 
insert into @t select'嗭','cis' 
insert into @t select'从','cong' 
insert into @t select'凑','cou' 
insert into @t select'粗','cu' 
insert into @t select'汆','cuan' 
insert into @t select'崔','cui' 
insert into @t select'邨','cun' 
insert into @t select'瑳','cuo' 
insert into @t select'撮','chua' 
insert into @t select'咑','da' 
insert into @t select'呔','dai' 
insert into @t select'丹','dan' 
insert into @t select'当','dang' 
insert into @t select'刀','dao' 
insert into @t select'恴','de' 
insert into @t select'揼','dem' 
insert into @t select'扥','den' 
insert into @t select'灯','deng' 
insert into @t select'仾','di' 
insert into @t select'嗲','dia' 
insert into @t select'敁','dian' 
insert into @t select'刁','diao' 
insert into @t select'爹','die' 
insert into @t select'哋','dei' 
insert into @t select'嚸','dim' 
insert into @t select'丁','ding' 
insert into @t select'丟','diu' 
insert into @t select'东','dong' 
insert into @t select'吺','dou' 
insert into @t select'剢','du' 
insert into @t select'耑','duan' 
insert into @t select'叾','dug' 
insert into @t select'垖','dui' 
insert into @t select'吨','dun' 
insert into @t select'咄','duo' 
insert into @t select'妸','e' 
insert into @t select'奀','en' 
insert into @t select'鞥','eng' 
insert into @t select'仒','eo' 
insert into @t select'乻','eol' 
insert into @t select'旕','eos' 
insert into @t select'儿','er' 
insert into @t select'发','fa' 
insert into @t select'帆','fan' 
insert into @t select'匚','fang' 
insert into @t select'飞','fei' 
insert into @t select'吩','fen' 
insert into @t select'丰','feng' 
insert into @t select'瓰','fenwa' 
insert into @t select'覅','fiao' 
insert into @t select'仏','fo' 
insert into @t select'垺','fou' 
insert into @t select'夫','fu' 
insert into @t select'猤','fui' 
insert into @t select'旮','ga' 
insert into @t select'侅','gai' 
insert into @t select'甘','gan' 
insert into @t select'冈','gang' 
insert into @t select'皋','gao' 
insert into @t select'戈','ge' 
insert into @t select'给','gei' 
insert into @t select'根','gen' 
insert into @t select'更','geng' 
insert into @t select'啹','geu' 
insert into @t select'喼','gib' 
insert into @t select'嗰','go' 
insert into @t select'工','gong' 
insert into @t select'兝','gongfen' 
insert into @t select'兣','gongli' 
insert into @t select'勾','gou' 
insert into @t select'估','gu' 
insert into @t select'瓜','gua' 
insert into @t select'乖','guai' 
insert into @t select'关','guan' 
insert into @t select'光','guang' 
insert into @t select'归','gui' 
insert into @t select'丨','gun' 
insert into @t select'呙','guo' 
insert into @t select'妎','ha' 
insert into @t select'咍','hai' 
insert into @t select'乤','hal' 
insert into @t select'兯','han' 
insert into @t select'魧','hang' 
insert into @t select'茠','hao' 
insert into @t select'兞','haoke' 
insert into @t select'诃','he' 
insert into @t select'黒','hei' 
insert into @t select'拫','hen' 
insert into @t select'亨','heng' 
insert into @t select'囍','heui' 
insert into @t select'乊','ho' 
insert into @t select'乥','hol' 
insert into @t select'叿','hong' 
insert into @t select'齁','hou' 
insert into @t select'乎','hu' 
insert into @t select'花','hua' 
insert into @t select'徊','huai' 
insert into @t select'欢','huan' 
insert into @t select'巟','huang' 
insert into @t select'灰','hui' 
insert into @t select'昏','hun' 
insert into @t select'吙','huo' 
insert into @t select'嚿','geo' 
insert into @t select'夻','hwa' 
insert into @t select'丌','ji' 
insert into @t select'加','jia' 
insert into @t select'嗧','jialun' 
insert into @t select'戋','jian' 
insert into @t select'江','jiang' 
insert into @t select'艽','jiao' 
insert into @t select'阶','jie' 
insert into @t select'巾','jin' 
insert into @t select'坕','jing' 
insert into @t select'冂','jiong' 
insert into @t select'丩','jiu' 
insert into @t select'欍','jou' 
insert into @t select'凥','ju' 
insert into @t select'姢','juan' 
insert into @t select'噘','jue' 
insert into @t select'军','jun' 
insert into @t select'咔','ka' 
insert into @t select'开','kai' 
insert into @t select'乫','kal' 
insert into @t select'刊','kan' 
insert into @t select'冚','hem' 
insert into @t select'砊','kang' 
insert into @t select'尻','kao' 
insert into @t select'坷','ke' 
insert into @t select'肎','ken' 
insert into @t select'劥','keng' 
insert into @t select'巪','keo' 
insert into @t select'乬','keol' 
insert into @t select'唟','keos' 
insert into @t select'厼','keum' 
insert into @t select'怾','ki' 
insert into @t select'空','kong' 
insert into @t select'廤','kos' 
insert into @t select'抠','kou' 
insert into @t select'扝','ku' 
insert into @t select'夸','kua' 
insert into @t select'蒯','kuai' 
insert into @t select'宽','kuan' 
insert into @t select'匡','kuang' 
insert into @t select'亏','kui' 
insert into @t select'坤','kun' 
insert into @t select'拡','kuo' 
insert into @t select'穒','kweok' 
insert into @t select'垃','la' 
insert into @t select'来','lai' 
insert into @t select'兰','lan' 
insert into @t select'啷','lang' 
insert into @t select'捞','lao' 
insert into @t select'仂','le' 
insert into @t select'雷','lei' 
insert into @t select'塄','leng' 
insert into @t select'唎','li' 
insert into @t select'俩','lia' 
insert into @t select'嫾','lian' 
insert into @t select'簗','liang' 
insert into @t select'蹽','liao' 
insert into @t select'毟','lie' 
insert into @t select'厸','lin' 
insert into @t select'伶','ling' 
insert into @t select'溜','liu' 
insert into @t select'瓼','liwa' 
insert into @t select'囖','lo' 
insert into @t select'龙','long' 
insert into @t select'娄','lou' 
insert into @t select'噜','lu' 
insert into @t select'驴','lv' 
insert into @t select'寽','lue' 
insert into @t select'孪','luan' 
insert into @t select'掄','lun' 
insert into @t select'頱','luo' 
insert into @t select'呣','m' 
insert into @t select'妈','ma' 
insert into @t select'遤','hweong' 
insert into @t select'埋','mai' 
insert into @t select'颟','man' 
insert into @t select'牤','mang' 
insert into @t select'匁','mangmi' 
insert into @t select'猫','mao' 
insert into @t select'唜','mas' 
insert into @t select'庅','me' 
insert into @t select'呅','mei' 
insert into @t select'椚','men' 
insert into @t select'掹','meng' 
insert into @t select'踎','meo' 
insert into @t select'瞇','mi' 
insert into @t select'宀','mian' 
insert into @t select'喵','miao' 
insert into @t select'乜','mie' 
insert into @t select'瓱','miliklanm' 
insert into @t select'民','min' 
insert into @t select'名','ming' 
insert into @t select'谬','miu' 
insert into @t select'摸','mo' 
insert into @t select'乮','mol' 
insert into @t select'哞','mou' 
insert into @t select'母','mu' 
insert into @t select'旀','myeo' 
insert into @t select'丆','myeon' 
insert into @t select'椧','myeong' 
insert into @t select'拏','na' 
insert into @t select'腉','nai' 
insert into @t select'囡','nan' 
insert into @t select'囔','nang' 
insert into @t select'乪','keg' 
insert into @t select'孬','nao' 
insert into @t select'疒','ne' 
insert into @t select'娞','nei' 
insert into @t select'焾','nem' 
insert into @t select'嫩','nen' 
insert into @t select'莻','neus' 
insert into @t select'鈪','ngag' 
insert into @t select'銰','ngai' 
insert into @t select'啱','ngam' 
insert into @t select'妮','ni' 
insert into @t select'年','nian' 
insert into @t select'娘','niang' 
insert into @t select'茑','niao' 
insert into @t select'捏','nie' 
insert into @t select'脌','nin' 
insert into @t select'宁','ning' 
insert into @t select'牛','niu' 
insert into @t select'农','nong' 
insert into @t select'羺','nou' 
insert into @t select'奴','nu' 
insert into @t select'女','nv' 
insert into @t select'疟','nue' 
insert into @t select'瘧','nve' 
insert into @t select'奻','nuan' 
insert into @t select'黁','nun' 
insert into @t select'燶','nung' 
insert into @t select'挪','nuo' 
insert into @t select'筽','o' 
insert into @t select'夞','oes' 
insert into @t select'乯','ol' 
insert into @t select'鞰','on' 
insert into @t select'讴','ou' 
insert into @t select'妑','pa' 
insert into @t select'俳','pai' 
insert into @t select'磗','pak' 
insert into @t select'眅','pan' 
insert into @t select'乓','pang' 
insert into @t select'抛','pao' 
insert into @t select'呸','pei' 
insert into @t select'瓫','pen' 
insert into @t select'匉','peng' 
insert into @t select'浌','peol' 
insert into @t select'巼','phas' 
insert into @t select'闏','phdeng' 
insert into @t select'乶','phoi' 
insert into @t select'喸','phos' 
insert into @t select'丕','pi' 
insert into @t select'囨','pian' 
insert into @t select'缥','piao' 
insert into @t select'氕','pie' 
insert into @t select'丿','pianpang' 
insert into @t select'姘','pin' 
insert into @t select'乒','ping' 
insert into @t select'钋','po' 
insert into @t select'剖','pou' 
insert into @t select'哣','deo' 
insert into @t select'兺','ppun' 
insert into @t select'仆','pu' 
insert into @t select'七','qi' 
insert into @t select'掐','qia' 
insert into @t select'千','qian' 
insert into @t select'羌','qiang' 
insert into @t select'兛','qianke' 
insert into @t select'瓩','qianwa' 
insert into @t select'悄','qiao' 
insert into @t select'苆','qie' 
insert into @t select'亲','qin' 
insert into @t select'蠄','kem' 
insert into @t select'氢','qing' 
insert into @t select'銎','qiong' 
insert into @t select'丘','qiu' 
insert into @t select'曲','qu' 
insert into @t select'迲','keop' 
insert into @t select'峑','quan' 
insert into @t select'蒛','que' 
insert into @t select'夋','qun' 
insert into @t select'亽','ra' 
insert into @t select'囕','ram' 
insert into @t select'呥','ran' 
insert into @t select'穣','rang' 
insert into @t select'荛','rao' 
insert into @t select'惹','re' 
insert into @t select'人','ren' 
insert into @t select'扔','reng' 
insert into @t select'日','ri' 
insert into @t select'栄','rong' 
insert into @t select'禸','rou' 
insert into @t select'嶿','ru' 
insert into @t select'撋','ruan' 
insert into @t select'桵','rui' 
insert into @t select'闰','run' 
insert into @t select'叒','ruo' 
insert into @t select'仨','sa' 
insert into @t select'栍','saeng' 
insert into @t select'毢','sai' 
insert into @t select'虄','sal' 
insert into @t select'三','san' 
insert into @t select'桒','sang' 
insert into @t select'掻','sao' 
insert into @t select'色','se' 
insert into @t select'裇','sed' 
insert into @t select'聓','sei' 
insert into @t select'森','sen' 
insert into @t select'鬙','seng' 
insert into @t select'閪','seo' 
insert into @t select'縇','seon' 
insert into @t select'杀','sha' 
insert into @t select'筛','shai' 
insert into @t select'山','shan' 
insert into @t select'伤','shang' 
insert into @t select'弰','shao' 
insert into @t select'奢','she' 
insert into @t select'申','shen' 
insert into @t select'升','sheng' 
insert into @t select'尸','shi' 
insert into @t select'兙','shike' 
insert into @t select'瓧','shiwa' 
insert into @t select'収','shou' 
insert into @t select'书','shu' 
insert into @t select'刷','shua' 
insert into @t select'摔','shuai' 
insert into @t select'闩','shuan' 
insert into @t select'双','shuang' 
insert into @t select'谁','shei' 
insert into @t select'脽','shui' 
insert into @t select'吮','shun' 
insert into @t select'哾','shuo' 
insert into @t select'丝','si' 
insert into @t select'螦','so' 
insert into @t select'乺','sol' 
insert into @t select'忪','song' 
insert into @t select'凁','sou' 
insert into @t select'苏','su' 
insert into @t select'痠','suan' 
insert into @t select'夊','sui' 
insert into @t select'孙','sun' 
insert into @t select'娑','suo' 
insert into @t select'他','ta' 
insert into @t select'襨','tae' 
insert into @t select'囼','tai' 
insert into @t select'坍','tan' 
insert into @t select'铴','tang' 
insert into @t select'仐','tao' 
insert into @t select'畓','tap' 
insert into @t select'忒','te' 
insert into @t select'膯','teng' 
insert into @t select'唞','teo' 
insert into @t select'朰','teul' 
insert into @t select'剔','ti' 
insert into @t select'天','tian' 
insert into @t select'旫','tiao' 
insert into @t select'怗','tie' 
insert into @t select'厅','ting' 
insert into @t select'乭','tol' 
insert into @t select'囲','tong' 
insert into @t select'偷','tou' 
insert into @t select'凸','tu' 
insert into @t select'湍','tuan' 
insert into @t select'推','tui' 
insert into @t select'旽','tun' 
insert into @t select'乇','tuo' 
insert into @t select'屲','wa' 
insert into @t select'歪','wai' 
insert into @t select'乛','wan' 
insert into @t select'尣','wang' 
insert into @t select'危','wei' 
insert into @t select'塭','wen' 
insert into @t select'翁','weng' 
insert into @t select'挝','wo' 
insert into @t select'乌','wu' 
insert into @t select'夕','xi' 
insert into @t select'诶','ei' 
insert into @t select'疨','xia' 
insert into @t select'仙','xian' 
insert into @t select'乡','xiang' 
insert into @t select'灱','xiao' 
insert into @t select'楔','xie' 
insert into @t select'心','xin' 
insert into @t select'星','xing' 
insert into @t select'凶','xiong' 
insert into @t select'休','xiu' 
insert into @t select'旴','xu' 
insert into @t select'昍','xuan' 
insert into @t select'疶','xue' 
insert into @t select'坃','xun' 
insert into @t select'丫','ya' 
insert into @t select'咽','yan' 
insert into @t select'欕','eom' 
insert into @t select'央','yang' 
insert into @t select'吆','yao' 
insert into @t select'椰','ye' 
insert into @t select'膶','yen' 
insert into @t select'一','yi' 
insert into @t select'乁','i' 
insert into @t select'乚','yin' 
insert into @t select'应','ying' 
insert into @t select'哟','yo' 
insert into @t select'佣','yong' 
insert into @t select'优','you' 
insert into @t select'迂','yu' 
insert into @t select'囦','yuan' 
insert into @t select'曰','yue' 
insert into @t select'蒀','yun' 
insert into @t select'帀','za' 
insert into @t select'災','zai' 
insert into @t select'兂','zan' 
insert into @t select'牂','zang' 
insert into @t select'遭','zao' 
insert into @t select'啫','ze' 
insert into @t select'贼','zei' 
insert into @t select'怎','zen' 
insert into @t select'曽','zeng' 
insert into @t select'吒','zha' 
insert into @t select'甴','gad' 
insert into @t select'夈','zhai' 
insert into @t select'毡','zhan' 
insert into @t select'张','zhang' 
insert into @t select'钊','zhao' 
insert into @t select'蜇','zhe' 
insert into @t select'贞','zhen' 
insert into @t select'凧','zheng' 
insert into @t select'之','zhi' 
insert into @t select'中','zhong' 
insert into @t select'州','zhou' 
insert into @t select'劯','zhu' 
insert into @t select'抓','zhua' 
insert into @t select'专','zhuan' 
insert into @t select'转','zhuai' 
insert into @t select'妆','zhuang' 
insert into @t select'骓','zhui' 
insert into @t select'宒','zhun' 
insert into @t select'卓','zhuo' 
insert into @t select'孜','zi' 
insert into @t select'唨','zo' 
insert into @t select'宗','zong' 
insert into @t select'棸','zou' 
insert into @t select'哫','zu' 
insert into @t select'劗','zuan' 
insert into @t select'厜','zui' 
insert into @t select'尊','zun' 
insert into @t select'昨','zuo' 

declare @strlen int 
select @strlen=len(@str),@re=''
while @strlen>0
begin 
select top 1

@re=UPPER(substring(py,1,1) )+substring(py,2,len(py))+' '+@re,@strlen=
@strlen-1 
from @t a where chr<=substring(@str,@strlen,1) 
order by chr collate Chinese_PRC_CS_AS_KS_WS desc 
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end


(鼠标移到代码上去,在代码的顶部会出现四个图标,第一个是查看源代码,第二个是复制代码,第三个是打印代码,第四个是帮助)
测试:
Select dbo.f_GetPy('中华人民共和国')
(鼠标移到代码上去,在代码的顶部会出现四个图标,第一个是查看源代码,第二个是复制代码,第三个是打印代码,第四个是帮助)
得到结果:Zhong Hua Ren Min Gong He Guo
如果想替换里面的空格就直接Replace下,和ASP里面的用法一样:
Select Replace(dbo.f_GetPy('中华人民共和国'),' ','')
(鼠标移到代码上去,在代码的顶部会出现四个图标,第一个是查看源代码,第二个是复制代码,第三个是打印代码,第四个是帮助)
得到结果:ZhongHuaRenMinGongHeGuo

关于我们-广告合作-联系我们-积分规则-网站地图

Copyright(C)2013-2017版权所属asp编程网