sql⾃动⽣成汉语拼⾳和⾸字母函数1.Sql server⾃动⽣成拼⾳的函数/*根据汉字获取全拼1.⽣成所有读⾳临时表2.根据Chinese_PRC_CS_AS_KS_WS 排序获取读⾳*/CREATE function f_GetPying(@str varchar(100))returns varchar(8000)asbegindeclare@re varchar(8000)--⽣成临时表declare@t table(chr nchar(1) collate Chinese_PRC_CS_AS_KS_WS,py nvarchar(20))insert into@t select'吖','a'insert into@t select'厑','aes'insert into@t select'哎','ai'insert into@t select'安','an'insert into@t select'肮','ang'insert into@t select'凹','ao'insert into@t select'⼋','ba'insert into@t select'挀','bai'insert into@t select'兡','baike'insert into@t select'瓸','baiwa'insert into@t select'扳','ban'insert into@t select'邦','bang'insert into@t select'⼓','bao'insert into@t select'萡','be'insert into@t select'陂','bei'insert into@t select'奔','ben'insert into@t select'伻','beng'insert into@t select'皀','bi'insert into@t select'边','bian'insert into@t select'辪','uu'insert into@t select'灬','biao'insert into@t select'憋','bie'insert into@t select'汃','bin'insert into@t select'⼎','bing'insert into@t select'⽨','bo'insert into@t select'峬','bu'insert into@t select'嚓','ca'insert into@t select'偲','cai'insert into@t select'乲','cal'insert into@t select'参','can'insert into@t select'仓','cang'insert into@t select'撡','cao'insert into@t select'冊','ce'insert into@t select'膥','cen'insert into@t select'噌','ceng'insert into@t select'硛','ceok'insert into@t select'岾','ceom'insert into@t select'猠','ceon'insert into@t select'乽','ceor'insert into@t select'叉','cha'insert into@t select'犲','chai'insert into@t select'辿','chan'insert into@t select'伥','chang'insert into@t select'抄','chao'insert into@t select'车','che'insert into@t select'抻','chen'insert into@t select'阷','cheng'insert into@t select'吃','chi'insert into@t select'充','chong'insert into@t select'抽','chou'insert into@t select'出','chu'insert into@t select'膗','chuai'insert into@t select'⼮','chuan'insert into@t select'刅','chuang'insert into@t select'吹','chui'insert into@t select'旾','chun'insert into@t select'踔','chuo'insert into@t select'呲','ci'insert into@t select'嗭','cis'insert into@t select'从','cong'insert into@t select'凑','cou'insert into@t select'粗','cu'insert into@t select'汆','cuan'insert into@t select'崔','cui'insert into@t select'邨','cun'insert into@t select'瑳','cuo'insert into@t select'撮','chua'insert into@t select'咑','da'insert into@t select'仾','di' insert into@t select'嗲','dia' insert into@t select'敁','dian' insert into@t select'刁','diao' insert into@t select'爹','die' insert into@t select'哋','dei' insert into@t select'嚸','dim' insert into@t select'丁','ding' insert into@t select'丟','diu' insert into@t select'东','dong' insert into@t select'吺','dou' insert into@t select'剢','du' insert into@t select'耑','duan' insert into@t select'叾','dug' insert into@t select'垖','dui' insert into@t select'吨','dun' insert into@t select'咄','duo' insert into@t select'妸','e' insert into@t select'奀','en' insert into@t select'鞥','eng' insert into@t select'仒','eo' insert into@t select'乻','eol' insert into@t select'旕','eos' insert into@t select'⼉','er' insert into@t select'发','fa' insert into@t select'帆','fan' insert into@t select'⼕','fang' insert into@t select'飞','fei' insert into@t select'吩','fen' insert into@t select'丰','feng' insert into@t select'瓰','fenwa' insert into@t select'覅','fiao' insert into@t select'仏','fo' insert into@t select'垺','fou' insert into@t select'夫','fu' insert into@t select'猤','fui' insert into@t select'旮','ga' insert into@t select'侅','gai' insert into@t select'⽢','gan' insert into@t select'冈','gang' insert into@t select'皋','gao' insert into@t select'⼽','ge' insert into@t select'给','gei' insert into@t select'根','gen' insert into@t select'更','geng' insert into@t select'啹','geu' insert into@t select'喼','gib' insert into@t select'嗰','go' insert into@t select'⼯','gong' insert into@t select'兝','gongfen' insert into@t select'兣','gongli' insert into@t select'勾','gou' insert into@t select'估','gu' insert into@t select'⽠','gua' insert into@t select'乖','guai' insert into@t select'关','guan' insert into@t select'光','guang' insert into@t select'归','gui' insert into@t select'⼁','gun' insert into@t select'呙','guo' insert into@t select'妎','ha' insert into@t select'咍','hai' insert into@t select'乤','hal' insert into@t select'兯','han' insert into@t select'魧','hang' insert into@t select'茠','hao' insert into@t select'兞','haoke' insert into@t select'诃','he' insert into@t select'黒','hei' insert into@t select'拫','hen' insert into@t select'亨','heng' insert into@t select'囍','heui' insert into@t select'乊','ho' insert into@t select'乥','hol' insert into@t select'叿','hong' insert into@t select'齁','hou' insert into@t select'乎','hu' insert into@t select'花','hua' insert into@t select'徊','huai' insert into@t select'欢','huan' insert into@t select'巟','huang'insert into@t select'戋','jian' insert into@t select'江','jiang' insert into@t select'艽','jiao' insert into@t select'阶','jie' insert into@t select'⼱','jin' insert into@t select'坕','jing' insert into@t select'⼌','jiong' insert into@t select'丩','jiu' insert into@t select'欍','jou' insert into@t select'凥','ju' insert into@t select'姢','juan' insert into@t select'噘','jue' insert into@t select'军','jun' insert into@t select'咔','ka' insert into@t select'开','kai' insert into@t select'乫','kal' insert into@t select'刊','kan' insert into@t select'冚','hem' insert into@t select'砊','kang' insert into@t select'尻','kao' insert into@t select'坷','ke' insert into@t select'肎','ken' insert into@t select'劥','keng' insert into@t select'巪','keo' insert into@t select'乬','keol' insert into@t select'唟','keos' insert into@t select'厼','keum' insert into@t select'怾','ki' insert into@t select'空','kong' insert into@t select'廤','kos' insert into@t select'抠','kou' insert into@t select'扝','ku' insert into@t select'夸','kua' insert into@t select'蒯','kuai' insert into@t select'宽','kuan' insert into@t select'匡','kuang' insert into@t select'亏','kui' insert into@t select'坤','kun' insert into@t select'拡','kuo' insert into@t select'穒','kweok' insert into@t select'垃','la' insert into@t select'来','lai' insert into@t select'兰','lan' insert into@t select'啷','lang' insert into@t select'捞','lao' insert into@t select'仂','le' insert into@t select'雷','lei' insert into@t select'塄','leng' insert into@t select'唎','li' insert into@t select'俩','lia' insert into@t select'嫾','lian' insert into@t select'簗','liang' insert into@t select'蹽','liao' insert into@t select'毟','lie' insert into@t select'厸','lin' insert into@t select'伶','ling' insert into@t select'溜','liu' insert into@t select'瓼','liwa' insert into@t select'囖','lo' insert into@t select'龙','long' insert into@t select'娄','lou' insert into@t select'噜','lu' insert into@t select'驴','lv' insert into@t select'寽','lue' insert into@t select'孪','luan' insert into@t select'掄','lun' insert into@t select'頱','luo' insert into@t select'呣','m' insert into@t select'妈','ma' insert into@t select'遤','hweong' insert into@t select'埋','mai' insert into@t select'颟','man' insert into@t select'牤','mang' insert into@t select'匁','mangmi' insert into@t select'猫','mao' insert into@t select'唜','mas' insert into@t select'庅','me' insert into@t select'呅','mei' insert into@t select'椚','men' insert into@t select'掹','meng' insert into@t select'踎','meo'insert into@t select'谬','miu' insert into@t select'摸','mo' insert into@t select'乮','mol' insert into@t select'哞','mou' insert into@t select'母','mu' insert into@t select'旀','myeo' insert into@t select'丆','myeon' insert into@t select'椧','myeong' insert into@t select'拏','na' insert into@t select'腉','nai' insert into@t select'囡','nan' insert into@t select'囔','nang' insert into@t select'乪','keg' insert into@t select'孬','nao' insert into@t select'⽧','ne' insert into@t select'娞','nei' insert into@t select'焾','nem' insert into@t select'嫩','nen' insert into@t select'莻','neus' insert into@t select'鈪','ngag' insert into@t select'銰','ngai' insert into@t select'啱','ngam' insert into@t select'妮','ni'insert into@t select'年','nian' insert into@t select'娘','niang' insert into@t select'茑','niao' insert into@t select'捏','nie' insert into@t select'脌','nin' insert into@t select'宁','ning' insert into@t select'⽜','niu' insert into@t select'农','nong' insert into@t select'羺','nou' insert into@t select'奴','nu' insert into@t select'⼥','nv' insert into@t select'疟','nue' insert into@t select'瘧','nve' insert into@t select'奻','nuan' insert into@t select'黁','nun' insert into@t select'燶','nung' insert into@t select'挪','nuo' insert into@t select'筽','o'insert into@t select'夞','oes' insert into@t select'乯','ol'insert into@t select'鞰','on' insert into@t select'讴','ou' insert into@t select'妑','pa' insert into@t select'俳','pai' insert into@t select'磗','pak' insert into@t select'眅','pan' insert into@t select'乓','pang' insert into@t select'抛','pao' insert into@t select'呸','pei' insert into@t select'瓫','pen' insert into@t select'匉','peng' insert into@t select'浌','peol' insert into@t select'巼','phas' insert into@t select'闏','phdeng' insert into@t select'乶','phoi' insert into@t select'喸','phos' insert into@t select'丕','pi'insert into@t select'囨','pian' insert into@t select'缥','piao' insert into@t select'氕','pie' insert into@t select'⼃','pianpang' insert into@t select'姘','pin' insert into@t select'乒','ping' insert into@t select'钋','po' insert into@t select'剖','pou' insert into@t select'哣','deo' insert into@t select'兺','ppun' insert into@t select'仆','pu' insert into@t select'七','qi'insert into@t select'掐','qia' insert into@t select'千','qian' insert into@t select'羌','qiang' insert into@t select'兛','qianke' insert into@t select'瓩','qianwa' insert into@t select'悄','qiao' insert into@t select'苆','qie' insert into@t select'亲','qin' insert into@t select'蠄','kem'insert into@t select'亽','ra' insert into@t select'囕','ram' insert into@t select'呥','ran' insert into@t select'穣','rang' insert into@t select'荛','rao' insert into@t select'惹','re' insert into@t select'⼈','ren' insert into@t select'扔','reng' insert into@t select'⽇','ri' insert into@t select'栄','rong' insert into@t select'⽱','rou' insert into@t select'嶿','ru' insert into@t select'撋','ruan' insert into@t select'桵','rui' insert into@t select'闰','run' insert into@t select'叒','ruo' insert into@t select'仨','sa' insert into@t select'栍','saeng' insert into@t select'毢','sai' insert into@t select'虄','sal' insert into@t select'三','san' insert into@t select'桒','sang' insert into@t select'掻','sao' insert into@t select'⾊','se' insert into@t select'裇','sed' insert into@t select'聓','sei' insert into@t select'森','sen' insert into@t select'鬙','seng' insert into@t select'閪','seo' insert into@t select'縇','seon' insert into@t select'杀','sha' insert into@t select'筛','shai' insert into@t select'⼭','shan' insert into@t select'伤','shang' insert into@t select'弰','shao' insert into@t select'奢','she' insert into@t select'申','shen' insert into@t select'升','sheng' insert into@t select'⼫','shi' insert into@t select'兙','shike' insert into@t select'瓧','shiwa' insert into@t select'収','shou' insert into@t select'书','shu' insert into@t select'刷','shua' insert into@t select'摔','shuai' insert into@t select'闩','shuan' insert into@t select'双','shuang' insert into@t select'谁','shei' insert into@t select'脽','shui' insert into@t select'吮','shun' insert into@t select'哾','shuo' insert into@t select'丝','si' insert into@t select'螦','so' insert into@t select'乺','sol' insert into@t select'忪','song' insert into@t select'凁','sou' insert into@t select'苏','su' insert into@t select'痠','suan' insert into@t select'⼢','sui' insert into@t select'孙','sun' insert into@t select'娑','suo' insert into@t select'他','ta' insert into@t select'襨','tae' insert into@t select'囼','tai' insert into@t select'坍','tan' insert into@t select'铴','tang' insert into@t select'仐','tao' insert into@t select'畓','tap' insert into@t select'忒','te' insert into@t select'膯','teng' insert into@t select'唞','teo' insert into@t select'朰','teul' insert into@t select'剔','ti' insert into@t select'天','tian' insert into@t select'旫','tiao' insert into@t select'怗','tie' insert into@t select'厅','ting' insert into@t select'乭','tol' insert into@t select'囲','tong' insert into@t select'偷','tou' insert into@t select'凸','tu'insert into@t select'危','wei' insert into@t select'塭','wen' insert into@t select'翁','weng' insert into@t select'挝','wo' insert into@t select'乌','wu' insert into@t select'⼣','xi' insert into@t select'诶','ei' insert into@t select'疨','xia' insert into@t select'仙','xian' insert into@t select'乡','xiang' insert into@t select'灱','xiao' insert into@t select'楔','xie' insert into@t select'⼼','xin' insert into@t select'星','xing' insert into@t select'凶','xiong' insert into@t select'休','xiu' insert into@t select'旴','xu' insert into@t select'昍','xuan' insert into@t select'疶','xue' insert into@t select'坃','xun' insert into@t select'丫','ya' insert into@t select'咽','yan' insert into@t select'欕','eom' insert into@t select'央','yang' insert into@t select'吆','yao' insert into@t select'椰','ye' insert into@t select'膶','yen' insert into@t select'⼀','yi' insert into@t select'乁','i' insert into@t select'乚','yin' insert into@t select'应','ying' insert into@t select'哟','yo' insert into@t select'佣','yong' insert into@t select'优','you' insert into@t select'迂','yu' insert into@t select'囦','yuan' insert into@t select'⽈','yue' insert into@t select'蒀','yun' insert into@t select'帀','za' insert into@t select'災','zai' insert into@t select'兂','zan' insert into@t select'牂','zang' insert into@t select'遭','zao' insert into@t select'啫','ze' insert into@t select'贼','zei' insert into@t select'怎','zen' insert into@t select'曽','zeng' insert into@t select'吒','zha' insert into@t select'甴','gad' insert into@t select'夈','zhai' insert into@t select'毡','zhan' insert into@t select'张','zhang' insert into@t select'钊','zhao' insert into@t select'蜇','zhe' insert into@t select'贞','zhen' insert into@t select'凧','zheng' insert into@t select'之','zhi' insert into@t select'中','zhong' insert into@t select'州','zhou' insert into@t select'劯','zhu' insert into@t select'抓','zhua' insert into@t select'专','zhuan' insert into@t select'转','zhuai' insert into@t select'妆','zhuang' insert into@t select'骓','zhui' insert into@t select'宒','zhun' insert into@t select'卓','zhuo' insert into@t select'孜','zi' insert into@t select'唨','zo' insert into@t select'宗','zong' insert into@t select'棸','zou' insert into@t select'哫','zu' insert into@t select'劗','zuan' insert into@t select'厜','zui' insert into@t select'尊','zun' insert into@t select'昨','zuo' declare@strlen intselect@strlen=len(@str),@re='' while@strlen>0beginselect@re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 endreturn(@re)end测试：SELECT dbo.fn_GetPying('中⽂字段') AS pymc2.sql server⾃动⽣成拼⾳⾸字母的函数--⽣成拼⾳⾸码CREATE function fn_GetPy(@str nvarchar(4000))returns nvarchar(4000)--WITH ENCRYPTIONasbegindeclare@intLen intdeclare@strRet nvarchar(4000)declare@temp nvarchar(100)set@intLen=len(@str)set@strRet=''while@intLen>0beginset@temp=''select@temp=casewhen substring(@str,@intLen,1) >='帀'then'Z'when substring(@str,@intLen,1) >='丫'then'Y'when substring(@str,@intLen,1) >='⼣'then'X'when substring(@str,@intLen,1) >='屲'then'W'when substring(@str,@intLen,1) >='他'then'T'when substring(@str,@intLen,1) >='仨'then'S'when substring(@str,@intLen,1) >='呥'then'R'when substring(@str,@intLen,1) >='七'then'Q'when substring(@str,@intLen,1) >='妑'then'P'when substring(@str,@intLen,1) >='噢'then'O'when substring(@str,@intLen,1) >='拏'then'N'when substring(@str,@intLen,1) >='嘸'then'M'when substring(@str,@intLen,1) >='垃'then'L'when substring(@str,@intLen,1) >='咔'then'K'when substring(@str,@intLen,1) >='丌'then'J'when substring(@str,@intLen,1) >='铪'then'H'when substring(@str,@intLen,1) >='旮'then'G'when substring(@str,@intLen,1) >='发'then'F'when substring(@str,@intLen,1) >='妸'then'E'when substring(@str,@intLen,1) >='咑'then'D'when substring(@str,@intLen,1) >='嚓'then'C'when substring(@str,@intLen,1) >='⼋'then'B'when substring(@str,@intLen,1) >='吖'then'A'else rtrim(ltrim(substring(@str,@intLen,1)))end--对于汉字特殊字符，不⽣成拼⾳码if (ascii(@temp)>127) set@temp=''--对于英⽂中⼩括号，不⽣成拼⾳码if@temp='('or@temp=')'set@temp=''select@strRet=@temp+@strRetset@intLen=@intLen-1endreturn lower(@strRet)end测试：SELECT dbo.fn_GetPy('中⽂字段') AS pymc。
