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

/*
sql 根据汉字获取全拼（有些字还没有添加上去，请自已加上去，涂聚文注）
生成所有读音临时表
根据chinese_prc_cs_as_ks_ws 排序获取读音
[e-mail] 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)
--生成临时表
[e-mail] 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