SQl语句取中文字的首字母拼音(自定义函数)
mysql数据库中查询汉字的拼音首字母
mysql数据库中查询汉字的拼⾳⾸字母本⼈提供的⽅法有如下特点：1.代码精简，使⽤简单，只要会基本的SQL语句就⾏2.不⽤建⽴mysql 函数等复杂的东西3.汉字库最全，可查询20902个汉字⽅法如下：1、建⽴拼⾳⾸字母资料表Sql代码：(最好再加上主键和索引)1. DROP TABLE IF EXISTS `pinyin`;2. CREATE TABLE `pinyin` (3. `PY` varchar(1),4. `HZ1` varchar(1),5. `HZ2` varchar(1)6. ) ;7.8. INSERT INTO `pinyin` (`PY`,`HZ1`,`HZ2`) VALUES9. ('A','吖','驁'),10. ('B','⼋','簿'),11. ('C','嚓','錯'),12. ('D','咑','鵽'),13. ('E','妸','樲'),14. ('F','发','鰒'),15. ('G','猤','腂'),16. ('H','妎','夻'),17. ('J','丌','攈'),18. ('K','咔','穒'),19. ('L','垃','鱳'),20. ('M','嘸','旀'),21. ('N','丆','桛'),22. ('O','噢','漚'),23. ('P','妑','曝'),24. ('Q','七','囕'),25. ('R','呥','鶸'),26. ('S','仨','蜶'),27. ('T','他','籜'),28. ('W','屲','鶩'),29. ('X','⼣','鑂'),30. ('Y','丫','韻'),31. ('Z','帀','咗');执⾏后检查添加的表中的数据记录是否有“？”问号，如果有则表⽰数据库编码有问题。
SQL批量重命名中文字段名为对应拼音首字母字段名脚本
SQL批量重命名中⽂字段名为对应拼⾳⾸字母字段名脚本操作步骤1:⽣成获取中⽂字符串对应的拼⾳⾸字母字符串函数操作步骤2:执⾏重命名中⽂字段名为拼⾳字⾸母字段名脚本--1、SQL获取中⽂字符串对应的拼⾳⾸字母字符串函数Create function fun_getPY(@str nvarchar(4000))returns nvarchar(4000)asbegindeclare @word nchar(1),@PY nvarchar(4000)set @PY=''while len(@str)>0beginset @word=left(@str,1)--如果⾮汉字字符，返回原字符set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901then (select top 1 PYfrom(select 'A' as PY,N'驁' as wordunion all select 'B',N'簿'union all select 'C',N'錯'union all select 'D',N'鵽'union all select 'E',N'樲'union all select 'F',N'鰒'union all select 'G',N'腂'union all select 'H',N'夻'union all select 'J',N'攈'union all select 'K',N'穒'union all select 'L',N'鱳'union all select 'M',N'旀'union all select 'N',N'桛'union all select 'O',N'漚'union all select 'P',N'曝'union all select 'Q',N'囕'union all select 'R',N'鶸'union all select 'S',N'蜶'union all select 'T',N'籜'union all select 'W',N'鶩'union all select 'X',N'鑂'union all select 'Y',N'韻'union all select 'Z',N'咗') Twhere word>=@word collate Chinese_PRC_CS_AS_KS_WSorder by PY ASC)else @wordend)set @str=right(@str,len(@str)-1)endreturn @PYend--2、SQL批量重命名中⽂字段名为对应拼⾳⾸字母字段名脚本--查询当前数据库中的所有中⽂字段名列表select as tablename, as columnname from sys.columns a left join sys.objects b on a.object_id=b.object_id where UNICODE() BETWEEN 19968 AND 19968+20901 order by --定义游标查询变量declare @TableName nvarchar(250)declare @ColumnName nvarchar(250)--声明读取数据库所有数据表名称游标mycursor1declare mycursor1 cursor for select as tablename, as columnname from sys.columns a left join sys.objects b on a.object_id=b.object_id where UNICODE() BETWEEN 19968 AND 19968+20901 order by --打开游标open mycursor1--从游标⾥取出数据赋值到我们刚才声明的数据表名变量中fetch next from mycursor1 into @TableName,@ColumnName--如果游标执⾏成功while (@@fetch_status=0)begin--定义重命名字段变量declare @NewColumnName varchar (250)select @NewColumnName=dbo.fun_getPY(@ColumnName)declare @renameCommand nvarchar(2000)set @renameCommand=@TableName+'.'+@ColumnName--指定重命名字段指令exec sp_rename @renameCommand,@NewColumnName,'column'--⽤游标去取下⼀条记录fetch next from mycursor1 into @TableName,@ColumnNameend--关闭游标close mycursor1--撤销游标deallocate mycursor1--查询重命名后的当前数据库中的所有中⽂字段名列表select as tablename, as columnname from sys.columns a left join sys.objects b on a.object_id=b.object_id where UNICODE() BETWEEN 19968 AND 19968+20901 order by 。
sql自动生成汉语拼音和首字母函数
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。
sql语句按照汉字拼音首字母排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
sqlserver：
select * from table order by name collate Chinese_PRC_CS_AS_KS_WS
举例如下：
表名为 dept ，其中name字段是中文，下面分别实现按照单位名称的笔划、部首和拼音排序。
--按照笔划排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
--按照部首排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
sql语句按照汉字拼音首字母排序
oracle ：
பைடு நூலகம்
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序，系统的默认排序方式为拼音排序
通过SQLServer取出汉字的拼音
通过SQL Server 取出汉字的拼音用排序规则特点计算汉字笔划和取得拼音首字母SQL SERVER勺排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到：SQL SERVER数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样勺错误：" 无法解决equal to 操作勺排序规则冲突。
"一. 错误分析：这个错误是因为排序规则不一致造成勺，我们做个测试，比如：create table #t1( name varchar(20) collate Albanian_CI_AI_WS, value int)create table #t2( name varchar(20) collate Chinese_PRC_CI_AI_WS, value int )表建好后，执行连接查询：select * from #t1 A inner join #t2 B on = 这样，错误就出现了：服务器：消息446 ，级别16 ，状态9 ，行1 无法解决equal to操作勺排序规则冲突。
要排除这个错误，最简单方法是，表连接时指定它勺排序规则，这样错误就不再出现了。
语句这样写：select * from #t1 A inner join #t2 B on = collate Chinese_PRC_CI_AI_WS二.排序规则简介：什么叫排序规则呢？MS是这样描述的："在Microsoft SQL Server 2000 中, 字符串勺物理存储由排序规则控制。
排序规则指定表示每个字符勺位模式以及存储和比较字符所使用的规则。
"在查询分析器内执行下面语句，可以得到SQL SERVE支持的所有排序规则。
select * from ：：fn_helpcollations()排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。
如：Chinese_PRC_CS_AI_WS前半部份：指UNICODE?符集，Chinese_PRCj f针对大陆简体字UNICODE勺排序规则。
SQL Server生成表中某列的拼音首码
生成某字段的拼音码---1、创建中间表（或导入数据）CREATE TABLE [tabpy] ([id] [int] IDENTITY (1, 1) NOT NULL ,[b_begin] [varbinary] (2) NULL ,[b_end] [varbinary] (2) NULL ,[word] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO--复制下面的文字到文本文件中，然后导入到tabpy表中"id","b_begin","b_end","word"1,B0A1,B0C4,"A"2,B0C5,B2C0,"B"3,B2C1,B4ED,"C"4,B4EE,B6E9,"D"5,B6EA,B7A1,"E"6,B7A2,B8C0,"F"7,B8C1,B9FD,"G"8,B9FE,BBF6,"H"9,BBF7,BFA5,"J"10,BFA6,C0AB,"K"11,C0AC,C2E7,"L"12,C2E8,C4C2,"M"13,C4C3,C5B5,"N"14,C5B6,C5BD,"O"15,C5BE,C6D9,"P"16,C6DA,C8BA,"Q"17,C8BB,C8F5,"R"18,C8F6,CBF9,"S"19,CBFA,CDD9,"T"20,CDDA,CEF3,"W"21,CEF4,D1B8,"X"22,D1B9,D4D0,"Y"23,D4D1,D7F9,"Z"----2、创建取拼音首字母的函数：CREATE function getfirstpy(@a varchar(200))returns varchar(100)asbegindeclare @i int,@j int,@result varchar(100),@pym varchar(1) set @result=''set @i=len(@a)set @j=1while @j<=@ibeginselect @result=@result+word from tabpywhere cast(substring(@a,@j,1) as varbinary(2))between b_begin and b_endor upper(substring(@a,@j,1)) = wordset @j=@j+1endreturn @resultend---3、函数的调用update mxzzy set 拼音码=lower(hisv11.dbo.getfirstpy(收费项目)) update mxzzy set 拼音码=自定义码 where 拼音码=''。
Sqlserver按汉字首字母排序（sql语句）
Sqlserver按汉字⾸字母排序（sql语句）在sql server中可以直接通过SQL语句实现按汉字⾸字母排序,⽐如我们经常⽤到的"按姓名⾸字母排序"--把tableName和colName换成⾃⼰的就ok了--按拼⾳ALTER TABLE tableNameALTER COLUMN colName nvarchar(100) COLLATE Chinese_PRC_CI_ASselect *from tableName order by colName--按笔画ALTER TABLE tableNameALTER COLUMN colName nvarchar(100) COLLATE Chinese_PRC_Stroke_CI_ASselect *from tableName order by colName说明：ALTER：修改表collate：是⼀个⼦句，可应⽤于数据库定义或列定义以定义排序规则，或应⽤于字符串表达式以应⽤排序规则投影。
Chinese_PRC_CI_AS：⼀种SQL排序规则下⾯简单介绍⼀下排序规则：什么叫排序规则呢？MS是这样描述的： "在 SQL Server 中，字符串的物理存储由排序规则控制。
排序规则指定表⽰每个字符的位模式以及存储和⽐较字符所使⽤的规则。
在查询分析器内执⾏下⾯语句，可以得到SQL SERVER⽀持的所有排序规则。
select * from ::fn_helpcollations() //sql server 有1011种排序规则排序规则名称由两部份构成，前半部份是指本排序规则所⽀持的字符集。
如： Chinese_PRC_CS_AI_WS前半部份：指UNICODE字符集，Chinese_PRC_指针对⼤陆简体字UNICODE的排序规则。
排序规则的后半部份即后缀含义： _BIN ⼆进制排序 _CI(CS) 是否区分⼤⼩写，CI不区分，CS区分 _AI(AS) 是否区分重⾳，AI不区分，AS区分 _KI(KS) 是否区分假名类型,KI不区分，KS区分_WI(WS) 是否区分宽度 WI不区分，WS区分区分⼤⼩写:如果想让⽐较将⼤写字母和⼩写字母视为不等，请选择该选项。
通过SQLServer取出汉字的拼音
SQL函数实现用拼音对汉字模糊查询方法一：create function f_GetPy(@Str nvarchar(400>> returns nvarchar(4000> asbegindeclare @strlen int,@re nvarchar(4000> declare @t table(chr nchar(1> collate Chinese_PRC_CI_AS,letter nchar(1>> insert @t select '吖','A' union all select '八','B' union all select '嚓','C' union all select '咑','D' union all select '妸','E' union all select '发','F' union all select '旮','G' union all select '铪','H' union all select '丌','J' union all select '咔','K' union all select '垃','L' union all select '嘸','M' union all select '拏','N' union all select '噢','O' union all select '妑','P' union all select '七','Q' union all select '呥','R' union all select '仨','S' union all select '他','T' union all select '屲','W' union all select '夕','X' union all select '丫','Y' union all select '帀','Z'select @strlen=len(@str>,@re=''while @strlen>0beginselect top 1 @re=letter+@re,@strlen=@strlen-1from @t a where chr<=substring(@str,@strlen,1>order by chr descif @@rowcount=0select @re=substring(@str,@strlen,1>+@re,@strlen=@strlen-1 endreturn(@re>endgo--测试select dbo.f_GetPy('东莞市'> as 东莞市,dbo.f_GetPy('ab中c 国人'> as 中国人b5E2RGbCAP--以后查询的时候,就可以调用上面的函数来实现汉字模糊查询select * from 表 where dbo.f_getpy(字段>='zgyh'方法二：CREATE FUNCTION f_GetPY(@str nvarchar(4000>>RETURNS nvarchar(4000>ASBEGINDECLARE @py TABLE(ch char(1>,hz1 nchar(1> COLLATE Chinese_PRC_CS_AS_KS_WS,hz2 nchar(1> COLLATE Chinese_PRC_CS_AS_KS_WS>INSERT @py SELECT 'A',N'吖',N'鏊'UNION ALL SELECT 'B',N'八',N'簿'UNION ALL SELECT 'C',N'嚓',N'错'UNION ALL SELECT 'D',N'哒',N'跺'UNION ALL SELECT 'E',N'屙',N'贰'UNION ALL SELECT 'F',N'发',N'馥'UNION ALL SELECT 'G',N'旮',N'过'UNION ALL SELECT 'H',N'铪',N'蠖'UNION ALL SELECT 'J',N'丌',N'竣'UNION ALL SELECT 'K',N'咔',N'廓'UNION ALL SELECT 'L',N'垃',N'雒'UNION ALL SELECT 'M',N'妈',N'穆'UNION ALL SELECT 'N',N'拿',N'糯'UNION ALL SELECT 'O',N'噢',N'沤'UNION ALL SELECT 'P',N'趴',N'曝'UNION ALL SELECT 'Q',N'七',N'群'UNION ALL SELECT 'R',N'蚺',N'箬'UNION ALL SELECT 'S',N'仨',N'锁'UNION ALL SELECT 'T',N'他',N'箨'UNION ALL SELECT 'W',N'哇',N'鋈'UNION ALL SELECT 'X',N'夕',N'蕈'UNION ALL SELECT 'Y',N'丫',N'蕴'UNION ALL SELECT 'Z',N'匝',N'做'DECLARE @i intSET @i=PATINDEX('%[吖-做]%' COLLATEChinese_PRC_CS_AS_KS_WS,@str>WHILE @i>0SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1>,ch> ,@i=PATINDEX('%[吖-做]%' COLLATEChinese_PRC_CS_AS_KS_WS,@str>FROM @pyWHERE SUBSTRING(@str,@i,1> BETWEEN hz1 AND hz2 RETURN(@str>ENDGO然后，使用查询语句select * from table wheresubstring(hoceedb.dbo.f_GetPY(str>,1,1> = 'A'查出table表中str字段第一个字符的拼音首字母等于‘A’的所有数据/*根据汉字获取全拼1.生成所有读音临时表2.根据Chinese_PRC_CS_AS_KS_WS 排序获取读音*/CREATE function f_GetPy(@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'兡','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'仓','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'巛','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'呔','dai' insert into @t select'丹','dan' insert into @t select'当','dang' insert into @t select'刀','dao' insert into @t select'恴','de'insert into @t select'扥','den' insert into @t select'灯','deng' 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'仒','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'给','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'魧','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'灰','hui' insert into @t select'昏','hun' insert into @t select'吙','huo' insert into @t select'嚿','geo'insert into @t select'丌','ji' insert into @t select'加','jia' insert into @t select'嗧','jialun' 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'尻','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'来','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'寽','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'瞇','mi' insert into @t select'宀','mian' insert into @t select'喵','miao'insert into @t select'瓱','miliklanm' insert into @t select'民','min' insert into @t select'冧','lem' insert into @t select'名','ming' 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'莻','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'筽','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'氕','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'氢','qing' insert into @t select'銎','qiong'insert into @t select'曲','qu' insert into @t select'迲','keop' insert into @t select'峑','quan' insert into @t select'蒛','que' insert into @t select'夋','qun' 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'栍','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'兙','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'他','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'湍','tuan'insert into @t select'旽','tun' insert into @t select'乇','tuo' insert into @t select'屲','wa' insert into @t select'歪','wai' insert into @t select'乛','wan' insert into @t select'尣','wang' 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'旴','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'帀','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'抓','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 int select @strlen=len(@str>,@re='' while @strlen>0beginselect top 1 p1EanqFDPw@re=UPPER(substring(py,1,1> >+substring(py,2,len(py>>+@re,@ strlen=DXDiTa9E3d@strlen-1from @t a where chr<=substring(@str,@strlen,1>order by chr collate Chinese_PRC_CS_AS_KS_WS descif @@rowcount=0select @re=substring(@str,@strlen,1>+@re,@strlen=@strlen-1end return(@re>endRTCrpUDGiT通过SQL Server取出汉字的拼音用排序规则特点计算汉字笔划和取得拼音首字母SQL SERVER的排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到: SQL SERVER数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样的错误： "无法解决 equal to 操作的排序规则冲突。
SQL如何让中文名字按名字第一个字的拼音首字母排序
博客园 用户登录 代码改变世界 密码登录 短信登录 忘记登录用户名 忘记密码 记住我 登何 让 中 文 名 字 按 名 字 第 一 个 字 的 拼 音 首 字 母 排 序
select isnull(b.py,upper(left(ername,1))) as py, ername from ( select 'a3' as username union select '中华人民共和国' union select '周笔畅' union select '郭安俊' union select '天堂' union select '包振' union select '魔兽' union select '精灵' union select '安全第一' ) a left outer join ( select 'A' as PY,N'驁' as word, N'啊' as sword union select 'B',N'簿',N'驁' union select 'C',N'錯',N'簿' union select 'D',N'鵽',N'錯' union select 'E',N'樲',N'鵽' union select 'F',N'鰒',N'樲' union select 'G',N'腂',N'鰒' union select 'H',N'夻',N'腂' union select 'J',N'攈',N'夻' union select 'K',N'穒',N'攈' union select 'L',N'鱳',N'穒' union select 'M',N'旀',N'鱳' union select 'N',N'桛',N'旀' union select 'O',N'漚',N'桛' union select 'P',N'曝',N'漚' union select 'Q',N'囕',N'曝' union select 'R',N'鶸',N'囕' union select 'S',N'蜶',N'鶸' union select 'T',N'籜',N'蜶' union select 'W',N'鶩',N'籜' union select 'X',N'鑂',N'鶩' union select 'Y',N'韻',N'鑂' union select 'Z',N'咗',N'韻' ) b on left(username,1) between b.sword and b.word
sql取拼音(全拼)
/*根据汉字获取全拼1.生成所有读音临时表2.根据Chinese_PRC_CS_AS_KS_WS 排序获取读音*/create function f_GetAllPy(@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'冊','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'呔','dai' insert into @t select'丹','dan' insert into @t select'当','dang' insert into @t select'刀','dao' insert into @t select'恴','de' insert into @t select'揼','dem' insert into @t select'扥','den'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'更','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'灰','hui' insert into @t select'昏','hun' insert into @t select'吙','huo' insert into @t select'嚿','geo' insert into @t select'夻','hwa' insert into @t select'丌','ji'insert into @t select'嗧','jialun' 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'啷','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'瞇','mi'insert into @t select'宀','mian' insert into @t select'喵','miao' insert into @t select'乜','mie'insert into @t select'瓱','miliklanm'insert into @t select'冧','lem' insert into @t select'名','ming' 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'乯','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'氢','qing' insert into @t select'銎','qiong' insert into @t select'丘','qiu' insert into @t select'曲','qu'insert into @t select'峑','quan' insert into @t select'蒛','que' insert into @t select'夋','qun' 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'収','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'湍','tuan' insert into @t select'推','tui' insert into @t select'旽','tun'insert into @t select'乇','tuo' insert into @t select'屲','wa' insert into @t select'歪','wai' insert into @t select'乛','wan' insert into @t select'尣','wang' 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'。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
create function fGetPy(@Str varchar(500)='')
returns varchar(500)
as
begin
declare@strlen int,@return varchar(500),@ii int
declare@n int,@c char(1),@chn nchar(1)
select@strlen=len(@str),@return='',@ii=0
set@ii=0
while@ii<@strlen
begin
select@ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if@chn>'z'
select@n=@n+1
,@c=case chn when@chn then char(@n)else@c end
from(
select top27*from(
select chn='吖'
union all select'八'
union all select'嚓'
union all select'咑'
union all select'妸'
union all select'发'
union all select'旮'
union all select'铪'
union all select'丌'--because have no 'i'
union all select'丌'
union all select'咔'
union all select'垃'
union all select'嘸'
union all select'拏'
union all select'噢'
union all select'妑'
union all select'七'
union all select'呥'
union all select'仨'
union all select'他'
union all select'屲'--no'u'
union all select'屲'--no'v'
union all select'屲'
union all select'夕'
union all select'丫'
union all select'帀'
union all select@chn)as a
order by chn COLLATE Chinese_PRC_CI_AS
)as b
else set@c='a'
set@return=@return+@c
end
return(@return)
end
go
--测试
select dbo.fgetpy('张三')as姓名拼音,dbo.fgetpy('x中y国人')as中国人。