注：可能因为sqlserver版本较低，执行错误，只需要把函数中的空格去掉，自己重新添加即可。
1.自动生成拼音的自定义函数
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create function [dbo].[f_GetPinYin](@str varchar(100)) returns varchar(8000) as begin declare @re varchar(8000) --生成临时表 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
SELECT * FROM dbo.Factory WHERE dbo.f_GetPinYin(Factory_Name) LIKE '%bo%'
原文链接：https://blog.csdn.net/qq_19879511/java/article/details/83626972
2.sqlserver 自定义生成拼音首字母函数
CREATE function fn_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
--WITH ENCRYPTION
as
begin
declare @intLen int
declare @strRet nvarchar(4000)
declare @temp nvarchar(100)
set @intLen = len(@str)
set @strRet = ''
while @intLen > 0
begin
set @temp = ''
select @temp = case
when 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 + @strRet
set @intLen = @intLen - 1
end
return lower(@strRet)
end
SELECT * FROM dbo.Factory WHERE dbo.fn_GetPy(Factory_Name) LIKE '%bsl%'