SQL 根据汉字获取全拼的代码

复制代码 代码如下:

/*

SQL 根据汉字获取全拼（有些字还没有添加上去，请自已加上去，涂聚文注）

生成所有读音临时表

根据Chinese_PRC_CS_AS_KS_WS 排序获取读音

geovindu@163.com 2009-12-2 其它功能請商討，我是在繁體的環境測試，簡體會更方便一些。

*/

if exists(select 1 from sysobjects where name = 'f_GetPy' and xtype = 'FN')

drop function f_GetPy

go

CREATE function f_GetPy(@str varchar(100))

returns varchar(8000)

as

begin

declare @re varchar(8000)

--生成临时表

--geovindu@163.com www.dusystem.com www.dupcit.com

declare @geovindu table(chr nchar(2) collate Chinese_PRC_CS_AS_KS_WS,py nvarchar(20))

insert into @geovindu select N'吖','a'

insert into @geovindu select N'厑','aes'

insert into @geovindu select N'哎','ai'

insert into @geovindu select N'安','an'

insert into @geovindu select N'肮','ang'

insert into @geovindu select N'凹','ao'

insert into @geovindu select N'八','ba'

insert into @geovindu select N'挀','bai'

insert into @geovindu select N'兡','baike'

insert into @geovindu select N'瓸','baiwa'

insert into @geovindu select N'扳','ban'

insert into @geovindu select N'邦','bang'

insert into @geovindu select N'勹','bao'

insert into @geovindu select N'萡','be'

insert into @geovindu select N'陂','bei'

insert into @geovindu select N'奔','ben'

insert into @geovindu select N'伻','beng'

insert into @geovindu select N'皀','bi'

insert into @geovindu select N'边','bian'

insert into @geovindu select N'辪','uu'

insert into @geovindu select N'灬','biao'

insert into @geovindu select N'憋','bie'

insert into @geovindu select N'汃','bin'

insert into @geovindu select N'冫','bing'

insert into @geovindu select N'癶','bo'

insert into @geovindu select N'峬','bu'

insert into @geovindu select N'嚓','ca'

insert into @geovindu select N'偲','cai'

insert into @geovindu select N'乲','cal'

insert into @geovindu select N'参','can'

insert into @geovindu select N'仓','cang'

insert into @geovindu select N'撡','cao'

insert into @geovindu select N'冊','ce'

insert into @geovindu select N'膥','cen'

insert into @geovindu select N'噌','ceng'

insert into @geovindu select N'硛','ceok'

insert into @geovindu select N'岾','ceom'

insert into @geovindu select N'猠','ceon'

insert into @geovindu select N'乽','ceor'

insert into @geovindu select N'叉','cha'

insert into @geovindu select N'犲','chai'

insert into @geovindu select N'辿','chan'

insert into @geovindu select N'伥','chang'

insert into @geovindu select N'抄','chao'

insert into @geovindu select N'车','che'

insert into @geovindu select N'抻','chen'

insert into @geovindu select N'阷','cheng'

insert into @geovindu select N'吃','chi'

insert into @geovindu select N'充','chong'

insert into @geovindu select N'抽','chou'

insert into @geovindu select N'出','chu'

insert into @geovindu select N'膗','chuai'

insert into @geovindu select N'巛','chuan'

insert into @geovindu select N'刅','chuang'

insert into @geovindu select N'吹','chui'

insert into @geovindu select N'旾','chun'

insert into @geovindu select N'踔','chuo'

insert into @geovindu select N'呲','ci'

insert into @geovindu select N'嗭','cis'

insert into @geovindu select N'从','cong'

insert into @geovindu select N'凑','cou'

insert into @geovindu select N'粗','cu'

insert into @geovindu select N'汆','cuan'

insert into @geovindu select N'崔','cui'

insert into @geovindu select N'邨','cun'

insert into @geovindu select N'瑳','cuo'

insert into @geovindu select N'撮','chua'

insert into @geovindu select N'咑','da'

insert into @geovindu select N'呔','dai'

insert into @geovindu select N'丹','dan'

insert into @geovindu select N'当','dang'

insert into @geovindu select N'刀','dao'

insert into @geovindu select N'恴','de'

insert into @geovindu select N'揼','dem'

insert into @geovindu select N'扥','den'

insert into @geovindu select N'灯','deng'

insert into @geovindu select N'仾','di'

insert into @geovindu select N'嗲','dia'

insert into @geovindu select N'敁','dian'

insert into @geovindu select N'刁','diao'

insert into @geovindu select N'爹','die'

insert into @geovindu select N'哋','dei'

insert into @geovindu select N'嚸','dim'

insert into @geovindu select N'丁','ding'

insert into @geovindu select N'丟','diu'

insert into @geovindu select N'东','dong'

insert into @geovindu select N'吺','dou'

insert into @geovindu select N'剢','du'

insert into @geovindu select N'耑','duan'

insert into @geovindu select N'叾','dug'

insert into @geovindu select N'垖','dui'

insert into @geovindu select N'吨','dun'

insert into @geovindu select N'咄','duo'

insert into @geovindu select N'妸','e'

insert into @geovindu select N'奀','en'

insert into @geovindu select N'鞥','eng'

insert into @geovindu select N'仒','eo'

insert into @geovindu select N'乻','eol'

insert into @geovindu select N'旕','eos'

insert into @geovindu select N'儿','er'

insert into @geovindu select N'发','fa'

insert into @geovindu select N'帆','fan'

insert into @geovindu select N'匚','fang'

insert into @geovindu select N'飞','fei'

insert into @geovindu select N'吩','fen'

insert into @geovindu select N'丰','feng'

insert into @geovindu select N'瓰','fenwa'

insert into @geovindu select N'覅','fiao'

insert into @geovindu select N'仏','fo'

insert into @geovindu select N'垺','fou'

insert into @geovindu select N'夫','fu'

insert into @geovindu select N'猤','fui'

insert into @geovindu select N'旮','ga'

insert into @geovindu select N'侅','gai'

insert into @geovindu select N'甘','gan'

insert into @geovindu select N'冈','gang'

insert into @geovindu select N'皋','gao'

insert into @geovindu select N'戈','ge'

insert into @geovindu select N'给','gei'

insert into @geovindu select N'根','gen'

insert into @geovindu select N'更','geng'

insert into @geovindu select N'啹','geu'

insert into @geovindu select N'喼','gib'

insert into @geovindu select N'嗰','go'

insert into @geovindu select N'工','gong'

insert into @geovindu select N'兝','gongfen'

insert into @geovindu select N'兣','gongli'

insert into @geovindu select N'勾','gou'

insert into @geovindu select N'估','gu'

insert into @geovindu select N'瓜','gua'

insert into @geovindu select N'乖','guai'

insert into @geovindu select N'关','guan'

insert into @geovindu select N'光','guang'

insert into @geovindu select N'归','gui'

insert into @geovindu select N'丨','gun'

insert into @geovindu select N'呙','guo'

insert into @geovindu select N'妎','ha'

insert into @geovindu select N'咍','hai'

insert into @geovindu select N'乤','hal'

insert into @geovindu select N'兯','han'

insert into @geovindu select N'魧','hang'

insert into @geovindu select N'茠','hao'

insert into @geovindu select N'兞','haoke'

insert into @geovindu select N'诃','he'

insert into @geovindu select N'黒','hei'

insert into @geovindu select N'拫','hen'

insert into @geovindu select N'亨','heng'

insert into @geovindu select N'囍','heui'

insert into @geovindu select N'乊','ho'

insert into @geovindu select N'乥','hol'

insert into @geovindu select N'叿','hong'

insert into @geovindu select N'齁','hou'

insert into @geovindu select N'乎','hu'

insert into @geovindu select N'花','hua'

insert into @geovindu select N'徊','huai'

insert into @geovindu select N'欢','huan'

insert into @geovindu select N'巟','huang'

insert into @geovindu select N'灰','hui'

insert into @geovindu select N'昏','hun'

insert into @geovindu select N'吙','huo'

insert into @geovindu select N'嚿','geo'

insert into @geovindu select N'夻','hwa'

insert into @geovindu select N'丌','ji'

insert into @geovindu select N'加','jia'

insert into @geovindu select N'嗧','jialun'

insert into @geovindu select N'戋','jian'

insert into @geovindu select N'江','jiang'

insert into @geovindu select N'艽','jiao'

insert into @geovindu select N'阶','jie'

insert into @geovindu select N'巾','jin'

insert into @geovindu select N'坕','jing'

insert into @geovindu select N'冂','jiong'

insert into @geovindu select N'丩','jiu'

insert into @geovindu select N'欍','jou'

insert into @geovindu select N'凥','ju'

insert into @geovindu select N'姢','juan'

insert into @geovindu select N'噘','jue'

insert into @geovindu select N'军','jun'

insert into @geovindu select N'咔','ka'

insert into @geovindu select N'开','kai'

insert into @geovindu select N'乫','kal'

insert into @geovindu select N'刊','kan'

insert into @geovindu select N'冚','hem'

insert into @geovindu select N'砊','kang'

insert into @geovindu select N'尻','kao'

insert into @geovindu select N'坷','ke'

insert into @geovindu select N'肎','ken'

insert into @geovindu select N'劥','keng'

insert into @geovindu select N'巪','keo'

insert into @geovindu select N'乬','keol'

insert into @geovindu select N'唟','keos'

insert into @geovindu select N'厼','keum'

insert into @geovindu select N'怾','ki'

insert into @geovindu select N'空','kong'

insert into @geovindu select N'廤','kos'

insert into @geovindu select N'抠','kou'

insert into @geovindu select N'扝','ku'

insert into @geovindu select N'夸','kua'

insert into @geovindu select N'蒯','kuai'

insert into @geovindu select N'宽','kuan'

insert into @geovindu select N'匡','kuang'

insert into @geovindu select N'亏','kui'

insert into @geovindu select N'坤','kun'

insert into @geovindu select N'拡','kuo'

insert into @geovindu select N'穒','kweok'

insert into @geovindu select N'垃','la'

insert into @geovindu select N'来','lai'

insert into @geovindu select N'兰','lan'

insert into @geovindu select N'啷','lang'

insert into @geovindu select N'捞','lao'

insert into @geovindu select N'仂','le'

insert into @geovindu select N'雷','lei'

insert into @geovindu select N'塄','leng'

insert into @geovindu select N'唎','li'

insert into @geovindu select N'俩','lia'

insert into @geovindu select N'嫾','lian'

insert into @geovindu select N'簗','liang'

insert into @geovindu select N'蹽','liao'

insert into @geovindu select N'毟','lie'

insert into @geovindu select N'厸','lin'

insert into @geovindu select N'伶','ling'

insert into @geovindu select N'溜','liu'

insert into @geovindu select N'瓼','liwa'

insert into @geovindu select N'囖','lo'

insert into @geovindu select N'龙','long'

insert into @geovindu select N'娄','lou'

insert into @geovindu select N'噜','lu'

insert into @geovindu select N'驴','lv'

insert into @geovindu select N'寽','lue'

insert into @geovindu select N'孪','luan'

insert into @geovindu select N'掄','lun'

insert into @geovindu select N'頱','luo'

insert into @geovindu select N'呣','m'

insert into @geovindu select N'妈','ma'

insert into @geovindu select N'遤','hweong'

insert into @geovindu select N'埋','mai'

insert into @geovindu select N'颟','man'

insert into @geovindu select N'牤','mang'

insert into @geovindu select N'匁','mangmi'

insert into @geovindu select N'猫','mao'

insert into @geovindu select N'唜','mas'

insert into @geovindu select N'庅','me'

insert into @geovindu select N'呅','mei'

insert into @geovindu select N'椚','men'

insert into @geovindu select N'掹','meng'

insert into @geovindu select N'踎','meo'

insert into @geovindu select N'瞇','mi'

insert into @geovindu select N'宀','mian'

insert into @geovindu select N'喵','miao'

insert into @geovindu select N'乜','mie'

insert into @geovindu select N'瓱','miliklanm'

insert into @geovindu select N'民','min'

insert into @geovindu select N'冧','lem'

insert into @geovindu select N'名','ming'

insert into @geovindu select N'谬','miu'

insert into @geovindu select N'摸','mo'

insert into @geovindu select N'乮','mol'

insert into @geovindu select N'哞','mou'

insert into @geovindu select N'母','mu'

insert into @geovindu select N'旀','myeo'

insert into @geovindu select N'丆','myeon'

insert into @geovindu select N'椧','myeong'

insert into @geovindu select N'拏','na'

insert into @geovindu select N'腉','nai'

insert into @geovindu select N'囡','nan'

insert into @geovindu select N'囔','nang'

insert into @geovindu select N'乪','keg'

insert into @geovindu select N'孬','nao'

insert into @geovindu select N'疒','ne'

insert into @geovindu select N'娞','nei'

insert into @geovindu select N'焾','nem'

insert into @geovindu select N'嫩','nen'

insert into @geovindu select N'莻','neus'

insert into @geovindu select N'鈪','ngag'

insert into @geovindu select N'銰','ngai'

insert into @geovindu select N'啱','ngam'

insert into @geovindu select N'妮','ni'

insert into @geovindu select N'年','nian'

insert into @geovindu select N'娘','niang'

insert into @geovindu select N'茑','niao'

insert into @geovindu select N'捏','nie'

insert into @geovindu select N'脌','nin'

insert into @geovindu select N'宁','ning'

insert into @geovindu select N'牛','niu'

insert into @geovindu select N'农','nong'

insert into @geovindu select N'羺','nou'

insert into @geovindu select N'奴','nu'

insert into @geovindu select N'女','nv'

insert into @geovindu select N'疟','nue'

insert into @geovindu select N'瘧','nve'

insert into @geovindu select N'奻','nuan'

insert into @geovindu select N'黁','nun'

insert into @geovindu select N'燶','nung'

insert into @geovindu select N'挪','nuo'

insert into @geovindu select N'筽','o'

insert into @geovindu select N'夞','oes'

insert into @geovindu select N'乯','ol'

insert into @geovindu select N'鞰','on'

insert into @geovindu select N'讴','ou'

insert into @geovindu select N'妑','pa'

insert into @geovindu select N'俳','pai'

insert into @geovindu select N'磗','pak'

insert into @geovindu select N'眅','pan'

insert into @geovindu select N'乓','pang'

insert into @geovindu select N'抛','pao'

insert into @geovindu select N'呸','pei'

insert into @geovindu select N'瓫','pen'

insert into @geovindu select N'匉','peng'

insert into @geovindu select N'浌','peol'

insert into @geovindu select N'巼','phas'

insert into @geovindu select N'闏','phdeng'

insert into @geovindu select N'乶','phoi'

insert into @geovindu select N'喸','phos'

insert into @geovindu select N'丕','pi'

insert into @geovindu select N'囨','pian'

insert into @geovindu select N'缥','piao'

insert into @geovindu select N'氕','pie'

insert into @geovindu select N'丿','pianpang'

insert into @geovindu select N'姘','pin'

insert into @geovindu select N'乒','ping'

insert into @geovindu select N'钋','po'

insert into @geovindu select N'剖','pou'

insert into @geovindu select N'哣','deo'

insert into @geovindu select N'兺','ppun'

insert into @geovindu select N'仆','pu'

insert into @geovindu select N'七','qi'

insert into @geovindu select N'掐','qia'

insert into @geovindu select N'千','qian'

insert into @geovindu select N'羌','qiang'

insert into @geovindu select N'兛','qianke'

insert into @geovindu select N'瓩','qianwa'

insert into @geovindu select N'悄','qiao'

insert into @geovindu select N'苆','qie'

insert into @geovindu select N'亲','qin'

insert into @geovindu select N'蠄','kem'

insert into @geovindu select N'氢','qing'

insert into @geovindu select N'銎','qiong'

insert into @geovindu select N'丘','qiu'

insert into @geovindu select N'曲','qu'

insert into @geovindu select N'迲','keop'

insert into @geovindu select N'峑','quan'

insert into @geovindu select N'蒛','que'

insert into @geovindu select N'夋','qun'

insert into @geovindu select N'亽','ra'

insert into @geovindu select N'囕','ram'

insert into @geovindu select N'呥','ran'

insert into @geovindu select N'穣','rang'

insert into @geovindu select N'荛','rao'

insert into @geovindu select N'惹','re'

insert into @geovindu select N'人','ren'

insert into @geovindu select N'扔','reng'

insert into @geovindu select N'日','ri'

insert into @geovindu select N'栄','rong'

insert into @geovindu select N'禸','rou'

insert into @geovindu select N'嶿','ru'

insert into @geovindu select N'撋','ruan'

insert into @geovindu select N'桵','rui'

insert into @geovindu select N'闰','run'

insert into @geovindu select N'叒','ruo'

insert into @geovindu select N'仨','sa'

insert into @geovindu select N'栍','saeng'

insert into @geovindu select N'毢','sai'

insert into @geovindu select N'虄','sal'

insert into @geovindu select N'三','san'

insert into @geovindu select N'桒','sang'

insert into @geovindu select N'掻','sao'

insert into @geovindu select N'色','se'

insert into @geovindu select N'裇','sed'

insert into @geovindu select N'聓','sei'

insert into @geovindu select N'森','sen'

insert into @geovindu select N'鬙','seng'

insert into @geovindu select N'閪','seo'

insert into @geovindu select N'縇','seon'

insert into @geovindu select N'杀','sha'

insert into @geovindu select N'筛','shai'

insert into @geovindu select N'山','shan'

insert into @geovindu select N'伤','shang'

insert into @geovindu select N'弰','shao'

insert into @geovindu select N'奢','she'

insert into @geovindu select N'申','shen'

insert into @geovindu select N'升','sheng'

insert into @geovindu select N'尸','shi'

insert into @geovindu select N'兙','shike'

insert into @geovindu select N'瓧','shiwa'

insert into @geovindu select N'収','shou'

insert into @geovindu select N'书','shu'

insert into @geovindu select N'刷','shua'

insert into @geovindu select N'摔','shuai'

insert into @geovindu select N'闩','shuan'

insert into @geovindu select N'双','shuang'

insert into @geovindu select N'谁','shei'

insert into @geovindu select N'脽','shui'

insert into @geovindu select N'吮','shun'

insert into @geovindu select N'哾','shuo'

insert into @geovindu select N'丝','si'

insert into @geovindu select N'螦','so'

insert into @geovindu select N'乺','sol'

insert into @geovindu select N'忪','song'

insert into @geovindu select N'凁','sou'

insert into @geovindu select N'苏','su'

insert into @geovindu select N'痠','suan'

insert into @geovindu select N'夊','sui'

insert into @geovindu select N'娑','suo'

insert into @geovindu select N'他','ta'

insert into @geovindu select N'襨','tae'

insert into @geovindu select N'囼','tai'

insert into @geovindu select N'坍','tan'

insert into @geovindu select N'铴','tang'

insert into @geovindu select N'仐','tao'

insert into @geovindu select N'畓','tap'

insert into @geovindu select N'忒','te'

insert into @geovindu select N'膯','teng'

insert into @geovindu select N'唞','teo'

insert into @geovindu select N'朰','teul'

insert into @geovindu select N'剔','ti'

insert into @geovindu select N'天','tian'

insert into @geovindu select N'旫','tiao'

insert into @geovindu select N'怗','tie'

insert into @geovindu select N'厅','ting'

insert into @geovindu select N'乭','tol'

insert into @geovindu select N'囲','tong'

insert into @geovindu select N'偷','tou'

insert into @geovindu select N'凸','tu'

insert into @geovindu select N'湍','tuan'

insert into @geovindu select N'推','tui'

insert into @geovindu select N'旽','tun'

insert into @geovindu select N'乇','tuo'

insert into @geovindu select N'屲','wa'

insert into @geovindu select N'歪','wai'

insert into @geovindu select N'乛','wan'

insert into @geovindu select N'尣','wang'

insert into @geovindu select N'危','wei'

insert into @geovindu select N'塭','wen'

insert into @geovindu select N'翁','weng'

insert into @geovindu select N'挝','wo'

insert into @geovindu select N'乌','wu'

insert into @geovindu select N'夕','xi'

insert into @geovindu select N'诶','ei'

insert into @geovindu select N'疨','xia'

insert into @geovindu select N'仙','xian'

insert into @geovindu select N'乡','xiang'

insert into @geovindu select N'灱','xiao'

insert into @geovindu select N'楔','xie'

insert into @geovindu select N'心','xin'

insert into @geovindu select N'星','xing'

insert into @geovindu select N'凶','xiong'

insert into @geovindu select N'休','xiu'

insert into @geovindu select N'旴','xu'

insert into @geovindu select N'昍','xuan'

insert into @geovindu select N'疶','xue'

insert into @geovindu select N'坃','xun'

insert into @geovindu select N'丫','ya'

insert into @geovindu select N'咽','yan'

insert into @geovindu select N'欕','eom'

insert into @geovindu select N'央','yang'

insert into @geovindu select N'涂','tu'

insert into @geovindu select N'聚','ju'

insert into @geovindu select N'文','wen'

insert into @geovindu select N'吆','yao'

insert into @geovindu select N'椰','ye'

insert into @geovindu select N'膶','yen'

insert into @geovindu select N'一','yi'

insert into @geovindu select N'乁','i'

insert into @geovindu select N'乚','yin'

insert into @geovindu select N'应','ying'

insert into @geovindu select N'哟','yo'

insert into @geovindu select N'佣','yong'

insert into @geovindu select N'优','you'

insert into @geovindu select N'迂','yu'

insert into @geovindu select N'囦','yuan'

insert into @geovindu select N'曰','yue'

insert into @geovindu select N'蒀','yun'

insert into @geovindu select N'帀','za'

insert into @geovindu select N'災','zai'

insert into @geovindu select N'兂','zan'

insert into @geovindu select N'牂','zang'

insert into @geovindu select N'遭','zao'

insert into @geovindu select N'啫','ze'

insert into @geovindu select N'贼','zei'

insert into @geovindu select N'怎','zen'

insert into @geovindu select N'曽','zeng'

insert into @geovindu select N'吒','zha'

insert into @geovindu select N'甴','gad'

insert into @geovindu select N'夈','zhai'

insert into @geovindu select N'毡','zhan'

insert into @geovindu select N'张','zhang'

insert into @geovindu select N'钊','zhao'

insert into @geovindu select N'蜇','zhe'

insert into @geovindu select N'贞','zhen'

insert into @geovindu select N'凧','zheng'

insert into @geovindu select N'之','zhi'

insert into @geovindu select N'中','zhong'

insert into @geovindu select N'州','zhou'

insert into @geovindu select N'劯','zhu'

insert into @geovindu select N'抓','zhua'

insert into @geovindu select N'专','zhuan'

insert into @geovindu select N'转','zhuai'

insert into @geovindu select N'妆','zhuang'

insert into @geovindu select N'骓','zhui'

insert into @geovindu select N'宒','zhun'

insert into @geovindu select N'卓','zhuo'

insert into @geovindu select N'孜','zi'

insert into @geovindu select N'唨','zo'

insert into @geovindu select N'宗','zong'

insert into @geovindu select N'棸','zou'

insert into @geovindu select N'哫','zu'

insert into @geovindu select N'劗','zuan'

insert into @geovindu select N'厜','zui'

insert into @geovindu select N'尊','zun'

insert into @geovindu select N'昨','zuo'

insert into @geovindu select N'孙','sun'

insert into @geovindu select N'斯','si'

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 @geovindu 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

GO

測試代碼如下：

--測試在功（其它应用于可想而知了，这里不多说了）

SELECT dbo.f_GetPy('人昨涂聚文宗斯博') as '拼音'

GO

測試代碼如下：

--測試在功（其它应用于可想而知了，这里不多说了）

SELECT dbo.f_GetPy('人昨涂聚文涂斯博') as '拼音'

GO

GO