chaos 表有一个 varchar 类型的字段 v,该字段的允许长度只有 15 位,但它存储的数据比较混杂。
chaos 表的数据:
id v ------ -------- 1 123 2 abc 3 1d3 4 0 5 123.0 6 0123 7 01#123 8 0$123
我们希望能从字段 v 的数据中提取出所有数字,重新组合成数值(数字在字符串中出现的相对顺序不变)。
期待得到的结果:
id v mix ------ ------ -------- 1 123 123 3 1d3 13 4 0 0 5 123.0 1230 6 0123 0123 7 01#123 01123 8 0$123 0123
一种可行的方法是:把原字符串拆分成一个个字符,然后过滤掉非数字字符,最后把剩下的数字按照出现的顺序组合成数值。
把字符串拆分成多个字符,可以使用递归的方式实现,也可以先和数字辅助表(有 1 ~ 15的自然数)做笛卡尔积连接,再分割出每个字符。
先来看比较简单的实现方案,也就是使用笛卡尔积的方案。
SELECT mix.id AS id, v, SUBSTR(v, t20.id, 1) AS s, t20.id AS seq FROM mix, t20 WHERE t20.id <= CHAR_LENGTH(v) ORDER BY mix.id, t20.id id v s seq ------ ------ ------ -------- 1 123 1 1 1 123 2 2 1 123 3 3 2 abc a 1 2 abc b 2 2 abc c 3 3 1d3 1 1 3 1d3 d 2 3 1d3 3 3 4 0 0 1 ...
其中,t20 是由 1 ~ 20 组成的数字辅助表。考虑到有可能出现中文汉字,在 SQL 中使用了 CHAR_LENGTH() 函数,而不是LENGTH()。
从打印的结果中可以看出,我们已经将字符串拆分成单个字符,并且还保持了字符的相对顺序。
最后,我们将非数字的字符过滤掉,再使用GROUP_CONCAT() 将数字字符拼接到一块。
SELECT id, v, GROUP_CONCAT(s ORDER BY seq SEPARATOR '') AS mix FROM (SELECT mix.id AS id, v, SUBSTR(v, t20.id, 1) AS s, t20.id AS seq FROM mix, t20 WHERE t20.id <= CHAR_LENGTH(v) ORDER BY v, t20.id) t WHERE s >= '0' AND s <= '9' GROUP BY v, id ORDER BY id
再来看递归的方式,它的实现有点复杂,我把完整的 SQL 放这了。
WITH RECURSIVE chaos (id, v, s, seq) AS (SELECT id, v, SUBSTR(v, 1, 1) AS s, 1 AS seq FROM mix UNION ALL SELECT id, v, SUBSTR(v, seq + 1, 1), seq + 1 FROM chaos WHERE seq <= CHAR_LENGTH(v) - 1) SELECT id, v, GROUP_CONCAT(s ORDER BY seq SEPARATOR '') AS mix FROM chaos WHERE s >= '0' AND s <= '9' GROUP BY v, id ORDER BY id