mysql - 查询将 5 个数字字符从字符串复制到 MySQL 中的单独列

Question

在我数据库的特定列中，我有很多字符串，例如

sometext sometext 12345 some more text 33 
some 12345 text then some random characters 12-13
text text text 3 text

现在我正在寻找一种方法来将 5 个数字字符的集合复制到一个单独的列中。我已经通过

缩小了我的选择范围

SELECT * FROM mytable WHERE mycolumn REGEXP '[0-9]{5}'

但这并不能隔离 5 个数字字符。有什么方法可以复制 5 个数字字符(如果存在)和 NULL(如果不存在(如第三行))？

Answer 1

表格:

CREATE TABLE table2 (`text` varchar(49));

INSERT INTO table2 (`text`)
VALUES
    ('sometext sometext 12345 some more text 33'),
    ('some 12345 text then some random characters 12-13'),
    ('text text text 3 text');

查询:

select
t.text,
substring_index(substring_index(t.text, ' ', n.number), ' ', -1) as 'extracted_number'
from
table2 as t,
(select 1 as 'number' union select 2 union select 3 union select 4 union select 5 union select 6  union select 7 union select 8)  as n
where 
t.text REGEXP '[0-9]{5}'
and substring_index(substring_index(t.text, ' ', n.number), ' ', -1) REGEXP '^[0-9]{5}$'

输出:

text                                                extracted_number
some 12345 text then some random characters 12-13   12345
sometext sometext 12345 some more text 33           12345

您可以将 select 1 as 'number' union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 替换为用数字填充的表格。最大数应等于一行中的最大单词数。

如果您可以使用 MariaDB 而不是 MySQL，那么您可以使用 sequence替换数字表甚至使用 REGEXP_INSTR (需要 MariaDB 10.0.5 或更高版本)而不是字符串函数。

更新:

以上查询将从给定文本中提取所有 5 位数字 - 而不仅仅是第一个。