SQL Server 排序规则(摘)

复制代码 代码如下: /* SQL
根据汉字获取全拼 生成所有读音临时表 根据Chinese_PRC_CS_AS_KS_WS
排序获取读音 geovindu@163.com 2009-12-2
其它功能請商討,我是在繁體的環境測試,簡體會更方便一些。 */ if
exists(select 1 from sysobjects where name = ‘f_GetPy’ and xtype =
‘FN’) drop function f_GetPy go CREATE function f_GetPy(@str
varchar(100)) returns varchar(8000) as begin declare @re varchar(8000)
–生成临时表 –geovindu@163.com declare @geovindu table(chr nchar(2)
collate Chinese_PRC_CS_AS_KS_WS,py nvarchar(20)) insert into
@geovindu select N’吖’,’a’ insert into @geovindu select N’厑’,’aes’
insert into @geovindu select N’哎’,’ai’ insert into @geovindu select
N’安’,’an’ insert into @geovindu select N’肮’,’ang’ insert into
@geovindu select N’凹’,’ao’ insert into @geovindu select N’八’,’ba’
insert into @geovindu select N’挀’,’bai’ insert into @geovindu select
N’兡’,’baike’ insert into @geovindu select N’瓸’,’baiwa’ insert into
@geovindu select N’扳’,’ban’ insert into @geovindu select N’邦’,’bang’
insert into @geovindu select N’勹’,’bao’ insert into @geovindu select
N’萡’,’be’ insert into @geovindu select N’陂’,’bei’ insert into
@geovindu select N’奔’,’ben’ insert into @geovindu select N’伻’,’beng’
insert into @geovindu select N’皀’,’bi’ insert into @geovindu select
N’边’,’bian’ insert into @geovindu select N’辪’,’uu’ insert into
@geovindu select N’灬’,’biao’ insert into @geovindu select N’憋’,’bie’
insert into @geovindu select N’汃’,’bin’ insert into @geovindu select
N’冫’,’bing’ insert into @geovindu select N’癶’,’bo’ insert into
@geovindu select N’峬’,’bu’ insert into @geovindu select N’嚓’,’ca’
insert into @geovindu select N’偲’,’cai’ insert into @geovindu select
N’乲’,’cal’ insert into @geovindu select N’参’,’can’ insert into
@geovindu select N’仓’,’cang’ insert into @geovindu select N’撡’,’cao’
insert into @geovindu select N’冊’,’ce’ insert into @geovindu select
N’膥’,’cen’ insert into @geovindu select N’噌’,’ceng’ insert into
@geovindu select N’硛’,’ceok’ insert into @geovindu select N’岾’,’ceom’
insert into @geovindu select N’猠’,’ceon’ insert into @geovindu select
N’乽’,’ceor’ insert into @geovindu select N’叉’,’cha’ insert into
@geovindu select N’犲’,’chai’ insert into @geovindu select N’辿’,’chan’
insert into @geovindu select N’伥’,’chang’ insert into @geovindu select
N’抄’,’chao’ insert into @geovindu select N’车’,’che’ insert into
@geovindu select N’抻’,’chen’ insert into @geovindu select N’阷’,’cheng’
insert into @geovindu select N’吃’,’chi’ insert into @geovindu select
N’充’,’chong’ insert into @geovindu select N’抽’,’chou’ insert into
@geovindu select N’出’,’chu’ insert into @geovindu select N’膗’,’chuai’
insert into @geovindu select N’巛’,’chuan’ insert into @geovindu select
N’刅’,’chuang’ insert into @geovindu select N’吹’,’chui’ insert into
@geovindu select N’旾’,’chun’ insert into @geovindu select N’踔’,’chuo’
insert into @geovindu select N’呲’,’ci’ insert into @geovindu select
N’嗭’,’cis’ insert into @geovindu select N’从’,’cong’ insert into
@geovindu select N’凑’,’cou’ insert into @geovindu select N’粗’,’cu’
insert into @geovindu select N’汆’,’cuan’ insert into @geovindu select
N’崔’,’cui’ insert into @geovindu select N’邨’,’cun’ insert into
@geovindu select N’瑳’,’cuo’ insert into @geovindu select N’撮’,’chua’
insert into @geovindu select N’咑’,’da’ insert into @geovindu select
N’呔’,’dai’ insert into @geovindu select N’丹’,’dan’ insert into
@geovindu select N’当’,’dang’ insert into @geovindu select N’刀’,’dao’
insert into @geovindu select N’恴’,’de’ insert into @geovindu select
N’揼’,’dem’ insert into @geovindu select N’扥’,’den’ insert into
@geovindu select N’灯’,’deng’ insert into @geovindu select N’仾’,’di’
insert into @geovindu select N’嗲’,’dia’ insert into @geovindu select
N’敁’,’dian’ insert into @geovindu select N’刁’,’diao’ insert into
@geovindu select N’爹’,’die’ insert into @geovindu select N’哋’,’dei’
insert into @geovindu select N’嚸’,’dim’ insert into @geovindu select
N’丁’,’ding’ insert into @geovindu select N’丟’,’diu’ insert into
@geovindu select N’东’,’dong’ insert into @geovindu select N’吺’,’dou’
insert into @geovindu select N’剢’,’du’ insert into @geovindu select
N’耑’,’duan’ insert into @geovindu select N’叾’,’dug’ insert into
@geovindu select N’垖’,’dui’ insert into @geovindu select N’吨’,’dun’
insert into @geovindu select N’咄’,’duo’ insert into @geovindu select
N’妸’,’e’ insert into @geovindu select N’奀’,’en’ insert into @geovindu
select N’鞥’,’eng’ insert into @geovindu select N’仒’,’eo’ insert into
@geovindu select N’乻’,’eol’ insert into @geovindu select N’旕’,’eos’
insert into @geovindu select N’儿’,’er’ insert into @geovindu select
N’发’,’fa’ insert into @geovindu select N’帆’,’fan’ insert into
@geovindu select N’匚’,’fang’ insert into @geovindu select N’飞’,’fei’
insert into @geovindu select N’吩’,’fen’ insert into @geovindu select
N’丰’,’feng’ insert into @geovindu select N’瓰’,’fenwa’ insert into
@geovindu select N’覅’,’fiao’ insert into @geovindu select N’仏’,’fo’
insert into @geovindu select N’垺’,’fou’ insert into @geovindu select
N’夫’,’fu’ insert into @geovindu select N’猤’,’fui’ insert into
@geovindu select N’旮’,’ga’ insert into @geovindu select N’侅’,’gai’
insert into @geovindu select N’甘’,’gan’ insert into @geovindu select
N’冈’,’gang’ insert into @geovindu select N’皋’,’gao’ insert into
@geovindu select N’戈’,’ge’ insert into @geovindu select N’给’,’gei’
insert into @geovindu select N’根’,’gen’ insert into @geovindu select
N’更’,’geng’ insert into @geovindu select N’啹’,’geu’ insert into
@geovindu select N’喼’,’gib’ insert into @geovindu select N’嗰’,’go’
insert into @geovindu select N’工’,’gong’ insert into @geovindu select
N’兝’,’gongfen’ insert into @geovindu select N’兣’,’gongli’ insert into
@geovindu select N’勾’,’gou’ insert into @geovindu select N’估’,’gu’
insert into @geovindu select N’瓜’,’gua’ insert into @geovindu select
N’乖’,’guai’ insert into @geovindu select N’关’,’guan’ insert into
@geovindu select N’光’,’guang’ insert into @geovindu select N’归’,’gui’
insert into @geovindu select N’丨’,’gun’ insert into @geovindu select
N’呙’,’guo’ insert into @geovindu select N’妎’,’ha’ insert into
@geovindu select N’咍’,’hai’ insert into @geovindu select N’乤’,’hal’
insert into @geovindu select N’兯’,’han’ insert into @geovindu select
N’魧’,’hang’ insert into @geovindu select N’茠’,’hao’ insert into
@geovindu select N’兞’,’haoke’ insert into @geovindu select N’诃’,’he’
insert into @geovindu select N’黒’,’hei’ insert into @geovindu select
N’拫’,’hen’ insert into @geovindu select N’亨’,’heng’ insert into
@geovindu select N’囍’,’heui’ insert into @geovindu select N’乊’,’ho’
insert into @geovindu select N’乥’,’hol’ insert into @geovindu select
N’叿’,’hong’ insert into @geovindu select N’齁’,’hou’ insert into
@geovindu select N’乎’,’hu’ insert into @geovindu select N’花’,’hua’
insert into @geovindu select N’徊’,’huai’ insert into @geovindu select
N’欢’,’huan’ insert into @geovindu select N’巟’,’huang’ insert into
@geovindu select N’灰’,’hui’ insert into @geovindu select N’昏’,’hun’
insert into @geovindu select N’吙’,’huo’ insert into @geovindu select
N’嚿’,’geo’ insert into @geovindu select N’夻’,’hwa’ insert into
@geovindu select N’丌’,’ji’ insert into @geovindu select N’加’,’jia’
insert into @geovindu select N’嗧’,’jialun’ insert into @geovindu select
N’戋’,’jian’ insert into @geovindu select N’江’,’jiang’ insert into
@geovindu select N’艽’,’jiao’ insert into @geovindu select N’阶’,’jie’
insert into @geovindu select N’巾’,’jin’ insert into @geovindu select
N’坕’,’jing’ insert into @geovindu select N’冂’,’jiong’ insert into
@geovindu select N’丩’,’jiu’ insert into @geovindu select N’欍’,’jou’
insert into @geovindu select N’凥’,’ju’ insert into @geovindu select
N’姢’,’juan’ insert into @geovindu select N’噘’,’jue’ insert into
@geovindu select N’军’,’jun’ insert into @geovindu select N’咔’,’ka’
insert into @geovindu select N’开’,’kai’ insert into @geovindu select
N’乫’,’kal’ insert into @geovindu select N’刊’,’kan’ insert into
@geovindu select N’冚’,’hem’ insert into @geovindu select N’砊’,’kang’
insert into @geovindu select N’尻’,’kao’ insert into @geovindu select
N’坷’,’ke’ insert into @geovindu select N’肎’,’ken’ insert into
@geovindu select N’劥’,’keng’ insert into @geovindu select N’巪’,’keo’
insert into @geovindu select N’乬’,’keol’ insert into @geovindu select
N’唟’,’keos’ insert into @geovindu select N’厼’,’keum’ insert into
@geovindu select N’怾’,’ki’ insert into @geovindu select N’空’,’kong’
insert into @geovindu select N’廤’,’kos’ insert into @geovindu select
N’抠’,’kou’ insert into @geovindu select N’扝’,’ku’ insert into
@geovindu select N’夸’,’kua’ insert into @geovindu select N’蒯’,’kuai’
insert into @geovindu select N’宽’,’kuan’ insert into @geovindu select
N’匡’,’kuang’ insert into @geovindu select N’亏’,’kui’ insert into
@geovindu select N’坤’,’kun’ insert into @geovindu select N’拡’,’kuo’
insert into @geovindu select N’穒’,’kweok’ insert into @geovindu select
N’垃’,’la’ insert into @geovindu select N’来’,’lai’ insert into
@geovindu select N’兰’,’lan’ insert into @geovindu select N’啷’,’lang’
insert into @geovindu select N’捞’,’lao’ insert into @geovindu select
N’仂’,’le’ insert into @geovindu select N’雷’,’lei’ insert into
@geovindu select N’塄’,’leng’ insert into @geovindu select N’唎’,’li’
insert into @geovindu select N’俩’,’lia’ insert into @geovindu select
N’嫾’,’lian’ insert into @geovindu select N’簗’,’liang’ insert into
@geovindu select N’蹽’,’liao’ insert into @geovindu select N’毟’,’lie’
insert into @geovindu select N’厸’,’lin’ insert into @geovindu select
N’伶’,’ling’ insert into @geovindu select N’溜’,’liu’ insert into
@geovindu select N’瓼’,’liwa’ insert into @geovindu select N’囖’,’lo’
insert into @geovindu select N’龙’,’long’ insert into @geovindu select
N’娄’,’lou’ insert into @geovindu select N’噜’,’lu’ insert into
@geovindu select N’驴’,’lv’ insert into @geovindu select N’寽’,’lue’
insert into @geovindu select N’孪’,’luan’ insert into @geovindu select
N’掄’,’lun’ insert into @geovindu select N’頱’,’luo’ insert into
@geovindu select N’呣’,’m’ insert into @geovindu select N’妈’,’ma’
insert into @geovindu select N’遤’,’hweong’ insert into @geovindu select
N’埋’,’mai’ insert into @geovindu select N’颟’,’man’ insert into
@geovindu select N’牤’,’mang’ insert into @geovindu select
N’匁’,’mangmi’ insert into @geovindu select N’猫’,’mao’ insert into
@geovindu select N’唜’,’mas’ insert into @geovindu select N’庅’,’me’
insert into @geovindu select N’呅’,’mei’ insert into @geovindu select
N’椚’,’men’ insert into @geovindu select N’掹’,’meng’ insert into
@geovindu select N’踎’,’meo’ insert into @geovindu select N’瞇’,’mi’
insert into @geovindu select N’宀’,’mian’ insert into @geovindu select
N’喵’,’miao’ insert into @geovindu select N’乜’,’mie’ insert into
@geovindu select N’瓱’,’miliklanm’ insert into @geovindu select
N’民’,’min’ insert into @geovindu select N’冧’,’lem’ insert into
@geovindu select N’名’,’ming’ insert into @geovindu select N’谬’,’miu’
insert into @geovindu select N’摸’,’mo’ insert into @geovindu select
N’乮’,’mol’ insert into @geovindu select N’哞’,’mou’ insert into
@geovindu select N’母’,’mu’ insert into @geovindu select N’旀’,’myeo’
insert into @geovindu select N’丆’,’myeon’ insert into @geovindu select
N’椧’,’myeong’ insert into @geovindu select N’拏’,’na’ insert into
@geovindu select N’腉’,’nai’ insert into @geovindu select N’囡’,’nan’
insert into @geovindu select N’囔’,’nang’ insert into @geovindu select
N’乪’,’keg’ insert into @geovindu select N’孬’,’nao’ insert into
@geovindu select N’疒’,’ne’ insert into @geovindu select N’娞’,’nei’
insert into @geovindu select N’焾’,’nem’ insert into @geovindu select
N’嫩’,’nen’ insert into @geovindu select N’莻’,’neus’ insert into
@geovindu select N’鈪’,’ngag’ insert into @geovindu select N’銰’,’ngai’
insert into @geovindu select N’啱’,’ngam’ insert into @geovindu select
N’妮’,’ni’ insert into @geovindu select N’年’,’nian’ insert into
@geovindu select N’娘’,’niang’ insert into @geovindu select N’茑’,’niao’
insert into @geovindu select N’捏’,’nie’ insert into @geovindu select
N’脌’,’nin’ insert into @geovindu select N’宁’,’ning’ insert into
@geovindu select N’牛’,’niu’ insert into @geovindu select N’农’,’nong’
insert into @geovindu select N’羺’,’nou’ insert into @geovindu select
N’奴’,’nu’ insert into @geovindu select N’女’,’nv’ insert into @geovindu
select N’疟’,’nue’ insert into @geovindu select N’瘧’,’nve’ insert into
@geovindu select N’奻’,’nuan’ insert into @geovindu select N’黁’,’nun’
insert into @geovindu select N’燶’,’nung’ insert into @geovindu select
N’挪’,’nuo’ insert into @geovindu select N’筽’,’o’ insert into @geovindu
select N’夞’,’oes’ insert into @geovindu select N’乯’,’ol’ insert into
@geovindu select N’鞰’,’on’ insert into @geovindu select N’讴’,’ou’
insert into @geovindu select N’妑’,’pa’ insert into @geovindu select
N’俳’,’pai’ insert into @geovindu select N’磗’,’pak’ insert into
@geovindu select N’眅’,’pan’ insert into @geovindu select N’乓’,’pang’
insert into @geovindu select N’抛’,’pao’ insert into @geovindu select
N’呸’,’pei’ insert into @geovindu select N’瓫’,’pen’ insert into
@geovindu select N’匉’,’peng’ insert into @geovindu select N’浌’,’peol’
insert into @geovindu select N’巼’,’phas’ insert into @geovindu select
N’闏’,’phdeng’ insert into @geovindu select N’乶’,’phoi’ insert into
@geovindu select N’喸’,’phos’ insert into @geovindu select N’丕’,’pi’
insert into @geovindu select N’囨’,’pian’ insert into @geovindu select
N’缥’,’piao’ insert into @geovindu select N’氕’,’pie’ insert into
@geovindu select N’丿’,’pianpang’ insert into @geovindu select
N’姘’,’pin’ insert into @geovindu select N’乒’,’ping’ insert into
@geovindu select N’钋’,’po’ insert into @geovindu select N’剖’,’pou’
insert into @geovindu select N’哣’,’deo’ insert into @geovindu select
N’兺’,’ppun’ insert into @geovindu select N’仆’,’pu’ insert into
@geovindu select N’七’,’qi’ insert into @geovindu select N’掐’,’qia’
insert into @geovindu select N’千’,’qian’ insert into @geovindu select
N’羌’,’qiang’ insert into @geovindu select N’兛’,’qianke’ insert into
@geovindu select N’瓩’,’qianwa’ insert into @geovindu select
N’悄’,’qiao’ insert into @geovindu select N’苆’,’qie’ insert into
@geovindu select N’亲’,’qin’ insert into @geovindu select N’蠄’,’kem’
insert into @geovindu select N’氢’,’qing’ insert into @geovindu select
N’銎’,’qiong’ insert into @geovindu select N’丘’,’qiu’ insert into
@geovindu select N’曲’,’qu’ insert into @geovindu select N’迲’,’keop’
insert into @geovindu select N’峑’,’quan’ insert into @geovindu select
N’蒛’,’que’ insert into @geovindu select N’夋’,’qun’ insert into
@geovindu select N’亽’,’ra’ insert into @geovindu select N’囕’,’ram’
insert into @geovindu select N’呥’,’ran’ insert into @geovindu select
N’穣’,’rang’ insert into @geovindu select N’荛’,’rao’ insert into
@geovindu select N’惹’,’re’ insert into @geovindu select N’人’,’ren’
insert into @geovindu select N’扔’,’reng’ insert into @geovindu select
N’日’,’ri’ insert into @geovindu select N’栄’,’rong’ insert into
@geovindu select N’禸’,’rou’ insert into @geovindu select N’嶿’,’ru’
insert into @geovindu select N’撋’,’ruan’ insert into @geovindu select
N’桵’,’rui’ insert into @geovindu select N’闰’,’run’ insert into
@geovindu select N’叒’,’ruo’ insert into @geovindu select N’仨’,’sa’
insert into @geovindu select N’栍’,’saeng’ insert into @geovindu select
N’毢’,’sai’ insert into @geovindu select N’虄’,’sal’ insert into
@geovindu select N’三’,’san’ insert into @geovindu select N’桒’,’sang’
insert into @geovindu select N’掻’,’sao’ insert into @geovindu select
N’色’,’se’ insert into @geovindu select N’裇’,’sed’ insert into
@geovindu select N’聓’,’sei’ insert into @geovindu select N’森’,’sen’
insert into @geovindu select N’鬙’,’seng’ insert into @geovindu select
N’閪’,’seo’ insert into @geovindu select N’縇’,’seon’ insert into
@geovindu select N’杀’,’sha’ insert into @geovindu select N’筛’,’shai’
insert into @geovindu select N’山’,’shan’ insert into @geovindu select
N’伤’,’shang’ insert into @geovindu select N’弰’,’shao’ insert into
@geovindu select N’奢’,’she’ insert into @geovindu select N’申’,’shen’
insert into @geovindu select N’升’,’sheng’ insert into @geovindu select
N’尸’,’shi’ insert into @geovindu select N’兙’,’shike’ insert into
@geovindu select N’瓧’,’shiwa’ insert into @geovindu select N’収’,’shou’
insert into @geovindu select N’书’,’shu’ insert into @geovindu select
N’刷’,’shua’ insert into @geovindu select N’摔’,’shuai’ insert into
@geovindu select N’闩’,’shuan’ insert into @geovindu select
N’双’,’shuang’ insert into @geovindu select N’谁’,’shei’ insert into
@geovindu select N’脽’,’shui’ insert into @geovindu select N’吮’,’shun’
insert into @geovindu select N’哾’,’shuo’ insert into @geovindu select
N’丝’,’si’ insert into @geovindu select N’螦’,’so’ insert into @geovindu
select N’乺’,’sol’ insert into @geovindu select N’忪’,’song’ insert into
@geovindu select N’凁’,’sou’ insert into @geovindu select N’苏’,’su’
insert into @geovindu select N’痠’,’suan’ insert into @geovindu select
N’夊’,’sui’ insert into @geovindu select N’娑’,’suo’ insert into
@geovindu select N’他’,’ta’ insert into @geovindu select N’襨’,’tae’
insert into @geovindu select N’囼’,’tai’ insert into @geovindu select
N’坍’,’tan’ insert into @geovindu select N’铴’,’tang’ insert into
@geovindu select N’仐’,’tao’ insert into @geovindu select N’畓’,’tap’
insert into @geovindu select N’忒’,’te’ insert into @geovindu select
N’膯’,’teng’ insert into @geovindu select N’唞’,’teo’ insert into
@geovindu select N’朰’,’teul’ insert into @geovindu select N’剔’,’ti’
insert into @geovindu select N’天’,’tian’ insert into @geovindu select
N’旫’,’tiao’ insert into @geovindu select N’怗’,’tie’ insert into
@geovindu select N’厅’,’ting’ insert into @geovindu select N’乭’,’tol’
insert into @geovindu select N’囲’,’tong’ insert into @geovindu select
N’偷’,’tou’ insert into @geovindu select N’凸’,’tu’ insert into
@geovindu select N’湍’,’tuan’ insert into @geovindu select N’推’,’tui’
insert into @geovindu select N’旽’,’tun’ insert into @geovindu select
N’乇’,’tuo’ insert into @geovindu select N’屲’,’wa’ insert into
@geovindu select N’歪’,’wai’ insert into @geovindu select N’乛’,’wan’
insert into @geovindu select N’尣’,’wang’ insert into @geovindu select
N’危’,’wei’ insert into @geovindu select N’塭’,’wen’ insert into
@geovindu select N’翁’,’weng’ insert into @geovindu select N’挝’,’wo’
insert into @geovindu select N’乌’,’wu’ insert into @geovindu select
N’夕’,’xi’ insert into @geovindu select N’诶’,’ei’ insert into @geovindu
select N’疨’,’xia’ insert into @geovindu select N’仙’,’xian’ insert into
@geovindu select N’乡’,’xiang’ insert into @geovindu select N’灱’,’xiao’
insert into @geovindu select N’楔’,’xie’ insert into @geovindu select
N’心’,’xin’ insert into @geovindu select N’星’,’xing’ insert into
@geovindu select N’凶’,’xiong’ insert into @geovindu select N’休’,’xiu’
insert into @geovindu select N’旴’,’xu’ insert into @geovindu select
N’昍’,’xuan’ insert into @geovindu select N’疶’,’xue’ insert into
@geovindu select N’坃’,’xun’ insert into @geovindu select N’丫’,’ya’
insert into @geovindu select N’咽’,’yan’ insert into @geovindu select
N’欕’,’eom’ insert into @geovindu select N’央’,’yang’ insert into
@geovindu select N’涂’,’tu’ insert into @geovindu select N’聚’,’ju’
insert into @geovindu select N’文’,’wen’ insert into @geovindu select
N’吆’,’yao’ insert into @geovindu select N’椰’,’ye’ insert into
@geovindu select N’膶’,’yen’ insert into @geovindu select N’一’,’yi’
insert into @geovindu select N’乁’,’i’ insert into @geovindu select
N’乚’,’yin’ insert into @geovindu select N’应’,’ying’ insert into
@geovindu select N’哟’,’yo’ insert into @geovindu select N’佣’,’yong’
insert into @geovindu select N’优’,’you’ insert into @geovindu select
N’迂’,’yu’ insert into @geovindu select N’囦’,’yuan’ insert into
@geovindu select N’曰’,’yue’ insert into @geovindu select N’蒀’,’yun’
insert into @geovindu select N’帀’,’za’ insert into @geovindu select
N’災’,’zai’ insert into @geovindu select N’兂’,’zan’ insert into
@geovindu select N’牂’,’zang’ insert into @geovindu select N’遭’,’zao’
insert into @geovindu select N’啫’,’ze’ insert into @geovindu select
N’贼’,’zei’ insert into @geovindu select N’怎’,’zen’ insert into
@geovindu select N’曽’,’zeng’ insert into @geovindu select N’吒’,’zha’
insert into @geovindu select N’甴’,’gad’ insert into @geovindu select
N’夈’,’zhai’ insert into @geovindu select N’毡’,’zhan’ insert into
@geovindu select N’张’,’zhang’ insert into @geovindu select N’钊’,’zhao’
insert into @geovindu select N’蜇’,’zhe’ insert into @geovindu select
N’贞’,’zhen’ insert into @geovindu select N’凧’,’zheng’ insert into
@geovindu select N’之’,’zhi’ insert into @geovindu select N’中’,’zhong’
insert into @geovindu select N’州’,’zhou’ insert into @geovindu select
N’劯’,’zhu’ insert into @geovindu select N’抓’,’zhua’ insert into
@geovindu select N’专’,’zhuan’ insert into @geovindu select
N’转’,’zhuai’ insert into @geovindu select N’妆’,’zhuang’ insert into
@geovindu select N’骓’,’zhui’ insert into @geovindu select N’宒’,’zhun’
insert into @geovindu select N’卓’,’zhuo’ insert into @geovindu select
N’孜’,’zi’ insert into @geovindu select N’唨’,’zo’ insert into @geovindu
select N’宗’,’zong’ insert into @geovindu select N’棸’,’zou’ insert into
@geovindu select N’哫’,’zu’ insert into @geovindu select N’劗’,’zuan’
insert into @geovindu select N’厜’,’zui’ insert into @geovindu select
N’尊’,’zun’ insert into @geovindu select N’昨’,’zuo’ insert into
@geovindu select N’孙’,’sun’ insert into @geovindu select N’斯’,’si’
declare @strlen int select @strlen=len(@str),@re=” while @strlen0 begin
select top 1 @re=UPPER(substring(py,1,1)
)+substring(py,2,len(py))+@re,@strlen=@strlen-1 from @geovindu a where
chr=substring(@str,@strlen,1) order by chr collate
Chinese_PRC_CS_AS_KS_WS desc if @@rowcount=0 select
@re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 end return(@re) end
GO 測試代碼如下: –測試在功 SELECT dbo.f_GetPy(‘人昨涂聚文宗斯博’) as
‘拼音’ GO 測試代碼如下: –測試在功 SELECT
dbo.f_GetPy(‘人昨涂聚文涂斯博’) as ‘拼音’ GO GO

3Sql
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 A.name=B.name
解决这个问题语句可以这样写:
select * from #t1 A inner join #t2 B on A.name=B.name 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的排序规则。
排序规则的后半部分含义:
   
_BIN二进制排序
   
_CI(CS)是否区分大小写,CI不区分,CS区分
   
_AI(AS)是否区分重音,AI不区分,AS区分
   
_KI(KS)是否区分假名类型,KI不区分,KS区分
   
_WI(WS)是否区分宽度,WI不区分,WS区分
区分大小写:是否想让比较将大写字母和小写字母视为不等
区分重音:是否想让比较将重音和非重音字母视为不等
区分假名:是否想让比较将片假名和平假名日语音节视为不等
区分宽度:是否想让比较将半角字符和全角字符视为不等

三、排序规则的应用:
例1:让表name列的内容按拼音排序
create table #t(id int,name varchar(20))
insert #t select 1,’中’
union all select 2,’国’
union all select 3,’人’
union all select 4,’阿’select * from #t order by name collate Chinese_PRC_CS_AS_KS_WS   
droptable #t
/*结果:
id         

发表评论

电子邮件地址不会被公开。 必填项已用*标注