我有一个MySQL数据库，并且我有一个查询：
SELECT `id`, `originaltext` FROM `source` WHERE `originaltext` regexp '[0-9][0-9]'
这会检测所有包含两位数字的原始文本。
我需要MySQL将这些数字作为一个字段返回，以便我可以进一步操作它们。
最好是，如果我可以添加额外的条件使它们>20，但我也可以单独完成这个条件。
如何在MySQL中从文本字段中提取连续的两个数字？
29
- Steve
7个回答
12
如果你想在数据库中使用更强大的正则表达式功能，可以考虑使用LIB_MYSQLUDF_PREG。这是一个MySQL用户函数的开源库，导入了PCRE库。LIB_MYSQLUDF_PREG只提供源代码形式。要使用它，您需要能够编译并将其安装到MySQL服务器中。安装此库不会以任何方式更改MySQL内置的正则表达式支持，它仅使以下其他函数可用：
PREG_CAPTURE从字符串中提取正则表达式匹配项。PREG_POSITION返回正则表达式匹配字符串的位置。PREG_REPLACE对字符串执行搜索和替换。PREG_RLIKE测试正则表达式是否与字符串匹配。
所有这些函数都以正则表达式作为它们的第一个参数。这个正则表达式必须像Perl正则表达式运算符一样格式化。例如，要测试正则表达式是否不区分大小写地匹配主题，您将使用MySQL代码PREG_RLIKE('/regex/i', subject)。这类似于PHP的preg函数，它们还需要PHP字符串内部的额外//分隔符来表示正则表达式。
如果您希望得到更简单的东西，您可以修改此函数以更好地满足您的需求。
所有这些函数都以正则表达式作为它们的第一个参数。这个正则表达式必须像Perl正则表达式运算符一样格式化。例如，要测试正则表达式是否不区分大小写地匹配主题，您将使用MySQL代码PREG_RLIKE('/regex/i', subject)。这类似于PHP的preg函数，它们还需要PHP字符串内部的额外//分隔符来表示正则表达式。
如果您希望得到更简单的东西，您可以修改此函数以更好地满足您的需求。
CREATE FUNCTION REGEXP_EXTRACT(string TEXT, exp TEXT)
-- Extract the first longest string that matches the regular expression
-- If the string is 'ABCD', check all strings and see what matches: 'ABCD', 'ABC', 'AB', 'A', 'BCD', 'BC', 'B', 'CD', 'C', 'D'
-- It's not smart enough to handle things like (A)|(BCD) correctly in that it will return the whole string, not just the matching token.
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE s INT DEFAULT 1;
DECLARE e INT;
DECLARE adjustStart TINYINT DEFAULT 1;
DECLARE adjustEnd TINYINT DEFAULT 1;
-- Because REGEXP matches anywhere in the string, and we only want the part that matches, adjust the expression to add '^' and '$'
-- Of course, if those are already there, don't add them, but change the method of extraction accordingly.
IF LEFT(exp, 1) = '^' THEN
SET adjustStart = 0;
ELSE
SET exp = CONCAT('^', exp);
END IF;
IF RIGHT(exp, 1) = '$' THEN
SET adjustEnd = 0;
ELSE
SET exp = CONCAT(exp, '$');
END IF;
-- Loop through the string, moving the end pointer back towards the start pointer, then advance the start pointer and repeat
-- Bail out of the loops early if the original expression started with '^' or ended with '$', since that means the pointers can't move
WHILE (s <= LENGTH(string)) DO
SET e = LENGTH(string);
WHILE (e >= s) DO
IF SUBSTRING(string, s, e) REGEXP exp THEN
RETURN SUBSTRING(string, s, e);
END IF;
IF adjustEnd THEN
SET e = e - 1;
ELSE
SET e = s - 1; -- ugh, such a hack to end it early
END IF;
END WHILE;
IF adjustStart THEN
SET s = s + 1;
ELSE
SET s = LENGTH(string) + 1; -- ugh, such a hack to end it early
END IF;
END WHILE;
RETURN NULL;
END
- Pentium10
1
嗯 - 我觉得在我的原始帖子中并不是很清楚，但原始文本周围有很多“废话”文字...我需要将数字“提取”出来。而且一个原始文本中可能会有多个数字... - Steve
9
MySQL中没有使用正则表达式提取文本的语法。您可以使用REGEXP来识别包含两个连续数字的行，但要提取它们，您必须使用普通字符串操作函数，在这种情况下非常困难。
替代方案：
- 从数据库中选择整个值，然后在客户端上使用正则表达式。
- 使用具有更好的SQL标准支持的不同数据库（可能不是一个选项，我知道）。然后您可以使用此代码：
SUBSTRING(originaltext from '%#[0-9]{2}#%' for '#')。
- Mark Byers
1
我同意。我的直觉是从数据库中获取整个字段，然后使用脚本将其与正则表达式进行比较并提取匹配项。 - dgmdan
3
我认为更加简洁的方法是使用REGEXP_SUBSTR()函数：
这将提取恰好两个数字：
SELECT REGEXP_SUBSTR(`originalText`,'[0-9]{2}') AS `twoDigits` FROM `source`;
这个函数从20到99中精确提取两个数字（例如：1112 返回 null; 1521 返回 52）：
SELECT REGEXP_SUBSTR(`originalText`,'[2-9][0-9]') AS `twoDigits` FROM `source`;
我在v8.0中测试过，它们都能正常工作。祝你好运！
- ESL
1
1不错。我用它将西班牙邮政编码从完整地址复制到新的列中...更新成员集合设置邮政编码= （选择REGEXP_SUBSTR('address'，'[0-9] {5}') AS'fiveDigits'）; - xgarb
2
我把我的代码作为存储过程（函数）使用，可以提取单个块中由数字构成的任何数字。这是我更广泛的库的一部分。
DELIMITER $$
-- 2013.04 michal@glebowski.pl
-- FindNumberInText("ab 234 95 cd", TRUE) => 234
-- FindNumberInText("ab 234 95 cd", FALSE) => 95
DROP FUNCTION IF EXISTS FindNumberInText$$
CREATE FUNCTION FindNumberInText(_input VARCHAR(64), _fromLeft BOOLEAN) RETURNS VARCHAR(32)
BEGIN
DECLARE _r VARCHAR(32) DEFAULT '';
DECLARE _i INTEGER DEFAULT 1;
DECLARE _start INTEGER DEFAULT 0;
DECLARE _IsCharNumeric BOOLEAN;
IF NOT _fromLeft THEN SET _input = REVERSE(_input); END IF;
_loop: REPEAT
SET _IsCharNumeric = LOCATE(MID(_input, _i, 1), "0123456789") > 0;
IF _IsCharNumeric THEN
IF _start = 0 THEN SET _start = _i; END IF;
ELSE
IF _start > 0 THEN LEAVE _loop; END IF;
END IF;
SET _i = _i + 1;
UNTIL _i > length(_input) END REPEAT;
IF _start > 0 THEN
SET _r = MID(_input, _start, _i - _start);
IF NOT _fromLeft THEN SET _r = REVERSE(_r); END IF;
END IF;
RETURN _r;
END$$
- m227
2
我有同样的问题，这是我找到的解决方法（但不适用于所有情况）：
- 使用
LOCATE()找到要匹配的字符串的开头和结尾 - 使用
MID()提取中间的子字符串... - 保留正则表达式只匹配您确定会找到匹配项的行。
- Greg
1
10一个例子可能能够帮助理解。 - Geoff
0
如果你想返回字符串的一部分：
SELECT id , substring(columnName,(locate('partOfString',columnName)),10) from tableName;
Locate()函数将返回匹配字符串的起始位置，这个位置也就是Function Substring()函数的起始位置。
- U.Sharma
0
我知道这个问题被问了很久，但是我偶然发现了它，认为这对我的自定义正则表达式替换器来说是一个不错的挑战 - 请查看此博客文章。
...好消息是可以做到，尽管需要调用多次。请参见此在线rextester演示, 它展示了得到下面SQL的工作原理。
SELECT reg_replace(
reg_replace(
reg_replace(
reg_replace(
reg_replace(
reg_replace(
reg_replace(txt,
'[^0-9]+',
',',
TRUE,
1, -- Min match length
0 -- No max match length
),
'([0-9]{3,}|,[0-9],)',
'',
TRUE,
1, -- Min match length
0 -- No max match length
),
'^[0-9],',
'',
TRUE,
1, -- Min match length
0 -- No max match length
),
',[0-9]$',
'',
TRUE,
1, -- Min match length
0 -- No max match length
),
',{2,}',
',',
TRUE,
1, -- Min match length
0 -- No max match length
),
'^,',
'',
TRUE,
1, -- Min match length
0 -- No max match length
),
',$',
'',
TRUE,
1, -- Min match length
0 -- No max match length
) AS `csv`
FROM tbl;
- Steve Chambers
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接