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'。
sql_server自动生成拼音码
sql server自动生成拼音首字母的函数建立一个查询，执行下面的语句生成函数fn_GetPy--生成拼音首码CREATE function getInputstr(@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。
通过SQL Server取出汉字的拼音
通过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 Bon = collate Chinese_PRC_CI_AI_WS二.排序规则简介：什么叫排序规则呢？MS是这样描述的："在 Microsoft SQL Server 2000 中，字符串的物理存储由排序规则控制。
排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。
"在查询分析器内执行下面语句，可以得到SQL SERVER支持的所有排序规则。
select * from ::fn_helpcollations()排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。
如：Chinese_PRC_CS_AI_WS前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。
通过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 Bon = collate Chinese_PRC_CI_AI_WS二.排序规则简介：什么叫排序规则呢？MS是这样描述的："在 Microsoft SQL Server 2000 中，字符串的物理存储由排序规则控制。
排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。
"在查询分析器内执行下面语句，可以得到SQL SERVER支持的所有排序规则。
select * from ::fn_helpcollations()排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。
如：Chinese_PRC_CS_AI_WS前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。
SQL自动生成拼音简码函数(函数名dbo.fun_getPY)
begin
declare @word nchar(1), @PY nvarchar(4000)
set @PY = ''
while len(@str) > 0
begin
set @word = left(@str, 1) --如果非汉字字符，返回原字符
set @PY = @PY +
select 'E',N'樲'
union all
select 'F',N'鰒'
union all
select 'G',N'腂'
union all
select 'H',N'夻'
union all
select 'J',N'攈'
--简体GBK码汉字UNICODE值从19968开始, WINDOWS多国汉字，UNICODE目前收录汉字共20902个
(case when unicode(@word) between 19968 and 19968 + 20901 then
(select top 1 PY from
if exists(select * from sysobjects where name = 'fun_getPY')
drop function fun_getPY
go
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
union all
实现SQL提取出字符串中的汉字、字母或是数字的函数
RETBEGIN
DECLARE@valuesVARCHAR(200)
DECLARE@value1INT
DECLARE@value2INT
SELECT@value1=PATINDEX('%[0-9.]%',@string)
SELECT@value2=PATINDEX('%[^0-9.]%',SUBSTRING(@string,PATINDEX('%[0-9.]%',@string),len(@string)-PATINDEX('%[0-9.]%',@string)+1))
实现sql提取出字符串中的汉字字母或是数字的函数提取字符串函数excel提取汉字首字母excel截取字符串函数mysql字符串函数excel连接字符串函数字符串函数字符串比较函数c语言字符串函数oracle字符串函数
实现SQL提取出字符串中的汉字、字母或是数字的函数
--创建函数(得到字符串中的汉字)
CREATEFUNCTION[dbo].[GetChinese]
(
@stringNVARCHAR(MAX)
)
RETURNSVARCHAR(200)
AS
BEGIN
WHILEPATINDEX('%[^吖-咗]%',@string)>0
BEGIN
SET@string=STUFF(@string,PATINDEX('%[^吖-咗]%',@string),1,N'');
IF@value2=0
BEGIN
SELECT@values=SUBSTRING(@string,@value1,len(@string)+1-@value1)
SQL取得汉字拼音首字母（转载）
SQL取得汉字拼⾳⾸字母（转载）create function GetPY(@str varchar(500))returns varchar(500)asbegindeclare@cyc int,@length int,@str1varchar(100),@charcate varbinary(20)set@cyc=1--从第⼏个字开始取set@length=len(@str)--输⼊汉字的长度set@str1=''--⽤于存放返回值while@cyc<=@lengthbeginselect@charcate=cast(substring(@str,@cyc,1) as varbinary)--每次取出⼀个字并将其转变成⼆进制,便于与GBK编码表进⾏⽐较if@charcate>=0XB0A1and@charcate<=0XB0C4set@str1=@str1+'A'--说明此汉字的⾸字母为A,以下同上else if@charcate>=0XB0C5and@charcate<=0XB2C0set@str1=@str1+'B'else if@charcate>=0XB2C1and@charcate<=0XB4EDset@str1=@str1+'C'else if@charcate>=0XB4EE and@charcate<=0XB6E9set@str1=@str1+'D'else if@charcate>=0XB6EA and@charcate<=0XB7A1set@str1=@str1+'E'else if@charcate>=0XB7A2and@charcate<=0XB8C0set@str1=@str1+'F'else if@charcate>=0XB8C1and@charcate<=0XB9FDset@str1=@str1+'G'else if@charcate>=0XB9FE and@charcate<=0XBBF6set@str1=@str1+'H'else if@charcate>=0XBBF7and@charcate<=0XBFA5set@str1=@str1+'J'else if@charcate>=0XBFA6and@charcate<=0XC0ABset@str1=@str1+'K'else if@charcate>=0XC0AC and@charcate<=0XC2E7set@str1=@str1+'L'else if@charcate>=0XC2E8and@charcate<=0XC4C2set@str1=@str1+'M'else if@charcate>=0XC4C3and@charcate<=0XC5B5set@str1=@str1+'N'else if@charcate>=0XC5B6and@charcate<=0XC5BDset@str1=@str1+'O'else if@charcate>=0XC5BE and@charcate<=0XC6D9set@str1=@str1+'P'else if@charcate>=0XC6DA and@charcate<=0XC8BAset@str1=@str1+'Q'else if@charcate>=0XC8BB and@charcate<=0XC8F5set@str1=@str1+'R'else if@charcate>=0XC8F6and@charcate<=0XCBF9set@str1=@str1+'S'else if@charcate>=0XCBFA and@charcate<=0XCDD9set@str1=@str1+'T'else if@charcate>=0XCDDA and@charcate<=0XCEF3set@str1=@str1+'W'else if@charcate>=0XCEF4and@charcate<=0XD1B8set@str1=@str1+'X'else if@charcate>=0XD1B9and@charcate<=0XD4D0set@str1=@str1+'Y'else if@charcate>=0XD4D1and@charcate<=0XD7F9set@str1=@str1+'Z'set@cyc=@cyc+1--取出输⼊汉字的下⼀个字endreturn@str1--返回输⼊汉字的⾸字母end--测试数据select dbo.GetPY('中华⼈民共和国')。
mysql 汉字首字母 函数
mysql 汉字首字母函数MySQL 汉字首字母函数是一种在MySQL 数据库中使用的函数，用于提取汉字字符串中每个汉字的首字母。
这个函数可以帮助我们进行数据分析、排序和检索等操作，提高数据处理效率。
下面是对MySQL 汉字首字母函数的详细介绍。
一、MySQL 汉字首字母函数的定义MySQL 汉字首字母函数是一个用户自定义函数，可以根据汉字的Unicode 编码值计算出每个汉字的首字母。
二、MySQL 汉字首字母函数的使用方法使用 MySQL 汉字首字母函数非常简单，只需在 SQL 查询语句中调用该函数，并将需要提取首字母的汉字作为参数传入即可。
例如，我们有一个名为"students"的表，其中有一个字段为"student_name"，存储了学生的姓名。
我们可以使用MySQL 汉字首字母函数来提取学生姓名的首字母，并按照首字母进行排序。
```SELECT student_name, first_letter(student_name) AS first_letter FROM studentsORDER BY first_letter;```上述 SQL 查询语句中，"first_letter" 函数用于提取"student_name"字段中每个学生姓名的首字母，并将其作为"first_letter"列返回。
然后，我们使用"ORDER BY"子句按照首字母进行排序。
三、MySQL 汉字首字母函数的实现原理MySQL 汉字首字母函数的实现原理是通过将汉字转换为Unicode 编码值，并根据编码值计算出汉字的首字母。
具体实现过程如下：1. 首先，将汉字转换为Unicode 编码值。
MySQL 中提供了内置函数"UNICODE"，可以将汉字转换为对应的 Unicode 编码值。
mysql 汉字首字母 函数
mysql 汉字首字母函数如果你想要从MySQL数据库中的汉字字段提取每个汉字的首字母，你可以使用以下的方法：1. 使用自定义函数:你可以创建一个自定义的MySQL函数来处理这个任务。
以下是一个简单的示例，它使用`SUBSTRING`和`CONVERT`函数来提取每个汉字的首字母：```sqlDELIMITER //CREATE FUNCTION get_chinese_initials(input_str VARCHAR(255)) RETURNS VARCHAR(255)BEGINDECLARE output_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 1;DECLARE len INT;DECLARE temp_str VARCHAR(2);SET len = LENGTH(input_str);WHILE i <= len DOSET temp_str = SUBSTRING(input_str, i, 1);SET output_str = CONCAT(output_str, SUBSTRING(UPPER(CONVERT(temp_str USING utf8)), 1, 1)); SET i = i + 1;END WHILE;RETURN output_str;END //DELIMITER ;```使用方法：```sqlSELECT get_chinese_initials('你好世界'); -- 输出: 'NH'```2. 使用现有工具或库:有一些现有的工具或库可以帮助你完成这个任务，例如使用Python的`py 汉子拼音`库，然后从数据库中提取数据，并在应用程序级别处理它。
这可能需要一些编程知识和集成，但它提供了更多的灵活性。
3. 注意:汉字到拼音的转换是一个复杂的过程，因为有很多同音字和多音字。
一个sql语句生成汉字首字母的函数
一个sql语句生成汉字首字母的函数USE [MyDB]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*获取汉字的首拼音如果是非汉字字符*/create function [dbo].[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 PY from(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_WS order by PY ASC)else @wordend)set @str=right(@str,len(@str)-1) endreturn upper(@PY)end。
- 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中国人。