我有一个MySQL数据库,并且我有一个查询:
SELECT `id`, `originaltext` FROM `source` WHERE `originaltext` regexp '[0-9][0-9]'
这会检测所有包含两位数字的原始文本。
我需要MySQL将这些数字作为一个字段返回,以便我可以进一步操作它们。
最好是,如果我可以添加额外的条件使它们>20,但我也可以单独完成这个条件。
我有一个MySQL数据库,并且我有一个查询:
SELECT `id`, `originaltext` FROM `source` WHERE `originaltext` regexp '[0-9][0-9]'
这会检测所有包含两位数字的原始文本。
我需要MySQL将这些数字作为一个字段返回,以便我可以进一步操作它们。
最好是,如果我可以添加额外的条件使它们>20,但我也可以单独完成这个条件。
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
MySQL中没有使用正则表达式提取文本的语法。您可以使用REGEXP来识别包含两个连续数字的行,但要提取它们,您必须使用普通字符串操作函数,在这种情况下非常困难。
替代方案:
SUBSTRING(originaltext from '%#[0-9]{2}#%' for '#')
。我认为更加简洁的方法是使用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中测试过,它们都能正常工作。祝你好运!
我把我的代码作为存储过程(函数)使用,可以提取单个块中由数字构成的任何数字。这是我更广泛的库的一部分。
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$$
我有同样的问题,这是我找到的解决方法(但不适用于所有情况):
LOCATE()
找到要匹配的字符串的开头和结尾MID()
提取中间的子字符串...如果你想返回字符串的一部分:
SELECT id , substring(columnName,(locate('partOfString',columnName)),10) from tableName;
Locate()
函数将返回匹配字符串的起始位置,这个位置也就是Function Substring()
函数的起始位置。
我知道这个问题被问了很久,但是我偶然发现了它,认为这对我的自定义正则表达式替换器来说是一个不错的挑战 - 请查看此博客文章。
...好消息是可以做到,尽管需要调用多次。请参见此在线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;