復制代碼 代碼如下:
/*
SQL 根據漢字獲取全拼（有些字還沒有添加上去，請自已加上去，塗聚文注）
生成所有讀音臨時表
根據Chinese_PRC_CS_AS_KS_WS 排序獲取讀音
[email protected] 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)
--生成臨時表
[email protected] 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
