中文首字母提取

利用漢字編碼邊界值提取

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

總結

以上是生活随笔為你收集整理的SQL 中文首字母提取与自定义排序的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。