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