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

/*
SQL 根据汉字获取全拼（有些字还没有添加上去，请自已加上去，涂聚文注）
生成所有读音临时表
根据Chinese_PRC_CS_AS_KS_WS 排序获取读音
geovindu@163.com 2009-12-2 其它功能?商?，我是在繁?w的?h境?y?，??w??更方便一些。
*/
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'?B','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'?n','bi'
insert into @geovindu select N'边','bian'
insert into @geovindu select N'?m','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'?h','bo'
insert into @geovindu select N'?m','bu'
insert into @geovindu select N'嚓','ca'
insert into @geovindu select N'??','cai'
insert into @geovindu select N'?k','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'?K','ceom'
insert into @geovindu select N'?e','ceon'
insert into @geovindu select N'?u','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'?p','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'?u','chuai'
insert into @geovindu select N'巛','chuan'
insert into @geovindu select N'?V','chuang'
insert into @geovindu select N'吹','chui'
insert into @geovindu select N'?I','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'?z','de'
insert into @geovindu select N'?g','dem'
insert into @geovindu select N'?O','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'?O','dei'
insert into @geovindu select N'??','dim'
insert into @geovindu select N'丁','ding'
insert into @geovindu select N'?G','diu'
insert into @geovindu select N'东','dong'
insert into @geovindu select N'??','dou'
insert into @geovindu select N'??','du'
insert into @geovindu select N'?Z','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'?C','en'
insert into @geovindu select N'?E','eng'
insert into @geovindu select N'??','eo'
insert into @geovindu select N'?s','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'?g','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'?o','ha'
insert into @geovindu select N'??','hai'
insert into @geovindu select N'?a','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'?Y','ho'
insert into @geovindu select N'?b','hol'
insert into @geovindu select N'??','hong'
insert into @geovindu select N'?J','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'?x','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'?i','jing'
insert into @geovindu select N'冂','jiong'
insert into @geovindu select N'?L','jiu'
insert into @geovindu select N'??','jou'
insert into @geovindu select N'?H','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'?f','kal'
insert into @geovindu select N'刊','kan'
insert into @geovindu select N'??','hem'
insert into @geovindu select N'?T','kang'
insert into @geovindu select N'尻','kao'
insert into @geovindu select N'坷','ke'
insert into @geovindu select N'?G','ken'
insert into @geovindu select N'??','keng'
insert into @geovindu select N'?|','keo'
insert into @geovindu select N'?g','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'?W','kos'
insert into @geovindu select N'抠','kou'
insert into @geovindu select N'?H','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'?i','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'?o','li'
insert into @geovindu select N'俩','lia'
insert into @geovindu select N'??','lian'
insert into @geovindu select N'?|','liang'
insert into @geovindu select N'?G','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'?F','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'?b','luo'
insert into @geovindu select N'??','m'
insert into @geovindu select N'妈','ma'
insert into @geovindu select N'?j','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'?x','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'?i','mol'
insert into @geovindu select N'哞','mou'
insert into @geovindu select N'母','mu'
insert into @geovindu select N'??','myeo'
insert into @geovindu select N'?C','myeon'
insert into @geovindu select N'??','myeong'
insert into @geovindu select N'??','na'
insert into @geovindu select N'?G','nai'
insert into @geovindu select N'囡','nan'
insert into @geovindu select N'囔','nang'
insert into @geovindu select N'?e','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'?G','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'?f','nuan'
insert into @geovindu select N'?Q','nun'
insert into @geovindu select N'?\','nung'
insert into @geovindu select N'挪','nuo'
insert into @geovindu select N'?p','o'
insert into @geovindu select N'??','oes'
insert into @geovindu select N'?j','ol'
insert into @geovindu select N'?M','on'
insert into @geovindu select N'讴','ou'
insert into @geovindu select N'?r','pa'
insert into @geovindu select N'俳','pai'
insert into @geovindu select N'?s','pak'
insert into @geovindu select N'?e','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'?C','phdeng'
insert into @geovindu select N'?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'?V','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'?j','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'?Z','quan'
insert into @geovindu select N'?U','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'?y','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'?b','rou'
insert into @geovindu select N'?]','ru'
insert into @geovindu select N'??','ruan'
insert into @geovindu select N'?M','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'?S','sed'
insert into @geovindu select N'?{','sei'
insert into @geovindu select N'森','sen'
insert into @geovindu select N'?O','seng'
insert into @geovindu select N'?~','seo'
insert into @geovindu select N'?L','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'?d','shuo'
insert into @geovindu select N'丝','si'
insert into @geovindu select N'?A','so'
insert into @geovindu select N'?r','sol'
insert into @geovindu select N'忪','song'
insert into @geovindu select N'??','sou'
insert into @geovindu select N'苏','su'
insert into @geovindu select N'?i','suan'
insert into @geovindu select N'??','sui'
insert into @geovindu select N'娑','suo'
insert into @geovindu select N'他','ta'
insert into @geovindu select N'?k','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'?z','tap'
insert into @geovindu select N'忒','te'
insert into @geovindu select N'?','teng'
insert into @geovindu select N'?z','teo'
insert into @geovindu select N'?Y','teul'
insert into @geovindu select N'剔','ti'
insert into @geovindu select N'天','tian'
insert into @geovindu select N'??','tiao'
insert into @geovindu select N'?G','tie'
insert into @geovindu select N'厅','ting'
insert into @geovindu select N'?h','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'?H','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'?e','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'?P','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'?B','xu'
insert into @geovindu select N'?R','xuan'
insert into @geovindu select N'?T','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'?U','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'?C','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'?h','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'?J','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'?d','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'?X','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