SQL 中文首字母提取与自定义排序

最新推荐文章于 2023-11-10 18:36:56 发布

原创最新推荐文章于 2023-11-10 18:36:56 发布 · 404 阅读

1 ·

CC 4.0 BY-SA版权

转载专栏收录该内容

4 篇文章

订阅专栏

中文首字母提取

利用汉字编码边界值提取

--中文转首字母
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);