需求： 用sql把一个字段中的中文和英文字符串拆分开来
例如： “魏派牌CC6460AE04A” =》 “魏派牌” “CC6460AE04A” “梅赛德斯-奔驰牌BJ6457H” =》 “梅赛德斯-奔驰牌” “BJ6457H” “博斯特A4121988CC小轿车” =》 “博斯特” “A4121988CC小轿车” “发现4SALAN2F6” =》 “发现” “4SALAN2F6”
用字典实现：
代码语言：javascript复制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码实现：
代码语言：javascript复制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;效果：
第二种使用ascii码判断的方法纯粹是作为SQL练习。从执行计划看明显字典方法更好，而且还不使用变量。MySQL 8中，SQL语句中使用变量是不推荐的过时方法，并且会报warning。
