一个有趣的MySQL拆分字符串需求

用户1148526

发布于 2022-04-13 10:11:48

1.6K00

代码可运行

文章被收录于专栏：Hadoop数据仓库Hadoop数据仓库

运行总次数：0

代码可运行

需求：用sql把一个字段中的中文和英文字符串拆分开来

例如： “魏派牌CC6460AE04A” =》 “魏派牌” “CC6460AE04A” “梅赛德斯-奔驰牌BJ6457H” =》 “梅赛德斯-奔驰牌” “BJ6457H” “博斯特A4121988CC小轿车” =》 “博斯特” “A4121988CC小轿车” “发现4SALAN2F6” =》 “发现” “4SALAN2F6”

用字典实现：

SELECT a, SUBSTRING(a, 1, b - 1) s1, SUBSTR(a, b) s2
  FROM (SELECT a, SUBSTRING_INDEX(b, ',', 1) b
          FROM (SELECT a,GROUP_CONCAT(b ORDER BY b) b
                  FROM (SELECT t1.a, INSTR(UPPER(t1.a), t2.a) b
                          FROM t1, 
                               (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a, ',', help_topic_id + 1), ',', - 1) a
                                  FROM mysql.help_topic, 
                                       (SELECT 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,0,1,2,3,4,5,6,7,8,9' a) t
                                 WHERE help_topic_id < (LENGTH(a) - LENGTH(REPLACE(a, ',', '')) + 1)) t2) t
                 WHERE b > 0 GROUP BY a) t) t;

效果：

用ascii码实现：

select a,group_concat(if (f=1,b,'') order by c separator '') s1, group_concat(if (f=0,b,'') order by c separator '') s2 
  from (select a,b,c,d,e,if (@b=a,@a:=least(e,@a),@a:=1)  f,@b:=a
          from ( select a,b,c,d,case when d between 65 and 90 or d between 48 and 57 then 0 else 1 end e   
                   from (select a,b,c,ascii(b) d
                           from (select a,substr(upper(a),help_topic_id+1,1) b, help_topic_id+1 c  
                                   from t1,mysql.help_topic where help_topic_id < char_length(a)) t) t) t1, 
       (select @a:=1,@b:='') t2) t group by a;