中文首字母提取
利用汉字编码边界值提取
select case when regexp_like( t. party_name, '^[a-zA-Z0-9]' ) then upper( substr( t. party_name, 0 , 1 ) )
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '吖' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '驁' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'A'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '八' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '簿' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'B'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '嚓' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '錯' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'C'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '咑' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '鵽' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'D'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '妸' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '樲' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'E'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '发' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '猤' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'F'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '旮' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '腂' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'G'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '妎' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '夻' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'H'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '丌' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '攈' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'J'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '咔' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '穒' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'K'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '垃' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '擽' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'L'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '嘸' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '椧' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'M'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '拏' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '瘧' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'N'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '筽' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '漚' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'O'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '妑' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '曝' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'P'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '七' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '裠' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'Q'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '亽' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '鶸' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'R'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '仨' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '蜶' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'S'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '侤' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '籜' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'T'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '屲' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '鶩' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'W'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '夕' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '鑂' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'X'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '丫' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '韻' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'Y'
when NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) >= NLSSORT( '帀' , 'NLS_SORT=SCHINESE_PINYIN_M' ) and
NLSSORT( t. party_name, 'NLS_SORT=SCHINESE_PINYIN_M' ) <= NLSSORT( '咗' , 'NLS_SORT=SCHINESE_PINYIN_M' ) then 'Z'
else substr( t. party_name, 0 , 1 )
end AS word, t. * from party. cb_group_party t ;
排序,a-z 0-9 特殊字符
select * from party. cb_group_party t order by translate( t. party_name, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' || t. party_name, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' ) nulls last ;
mysql版本
select * from test order by names REGEXP '^[0-9]', convert(names USING gbk);