SQL 从字符串中提取数字
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
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-11-26，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读