mix 表有一个 varchar
类型的字段 v,该字段的允许长度只有 15 位,但它存储的数据比较混杂。
mix 表的数据:
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