Как извлечь две последовательные цифры из текстового поля в MySQL?

Question

У меня есть база данных MySQL, и у меня есть запрос как:

SELECT `id`, `originaltext` FROM `source` WHERE `originaltext` regexp '[0-9][0-9]'

Это обнаруживает все оригинальные тексты, в которых есть номера с 2 цифрами.

Мне нужно, чтобы MySQL возвращал эти числа в виде поля, чтобы я мог манипулировать ими дальше.

В идеале, если бы я мог добавить дополнительные критерии, которые должны быть> 20, было бы здорово, но я могу сделать это и отдельно.

Answer 1

Если вам нужна большая мощность регулярных выражений в вашей базе данных, вы можете использовать LIB_MYSQLUDF_PREG. Это библиотека с открытым исходным кодом пользовательских функций MySQL, которая импортирует библиотеку PCRE. LIB_MYSQLUDF_PREG поставляется только в виде исходного кода. Чтобы использовать его, вам нужно будет скомпилировать его и установить на свой сервер MySQL. Установка этой библиотеки никоим образом не меняет встроенную поддержку регулярных выражений MySQL. Он просто делает доступными следующие дополнительные функции:

PREG_CAPTURE извлекает совпадение регулярного выражения из строки. PREG_POSITION возвращает позицию, в которой регулярное выражение соответствует строке. PREG_REPLACE выполняет поиск и замену строки. PREG_RLIKE проверяет, соответствует ли регулярное выражение строке.

Все эти функции принимают регулярное выражение в качестве первого параметра. Это регулярное выражение должно быть отформатировано как оператор регулярного выражения Perl. Например, чтобы проверить, соответствует ли регулярное выражение нечувствительному регистру, вы должны использовать код MySQL PREG_RLIKE('/regex/i', субъект). Это похоже на preg-функции PHP, которые также требуют дополнительных разделителей // для регулярных выражений внутри строки 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

Answer 2

В MySQL нет синтаксиса для извлечения текста с использованием регулярных выражений. Вы можете использовать REGEXP, чтобы идентифицировать строки, содержащие две последовательные цифры, но для их извлечения вы должны использовать обычные функции манипуляции со строками, что в этом случае очень сложно.

Альтернативы:

• Выберите все значение из базы данных, затем используйте регулярное выражение на клиенте.
• Используйте другую базу данных, которая лучше поддерживает стандарт SQL (я не знаю, как вариант). Тогда вы можете использовать это: SUBSTRING(originaltext from '%#[0-9]{2}#%' for '#'),

Answer 3

Я думаю, что более чистый способ использует REGEXP_SUBSTR():

Это извлекает ровно две любых цифры:

SELECT REGEXP_SUBSTR(`originalText`,'[0-9]{2}') AS `twoDigits` FROM `source`;

Это извлекает ровно две цифры, но от 20 до 99 (пример: 1112 вернуть ноль; 1521 возвращается 52):

SELECT REGEXP_SUBSTR(`originalText`,'[2-9][0-9]') AS `twoDigits` FROM `source`;

Я тестирую оба в v8.0, и они работают. Вот и все, удачи!

Answer 4

У меня та же проблема, и вот решение, которое я нашел (но оно не будет работать во всех случаях):

• использование LOCATE() чтобы найти начало и конец строки, которой вы не хотите соответствовать
• использование MID() извлечь подстроку между...
• сохраняйте регулярное выражение для соответствия только тем строкам, в которых вы обязательно найдете совпадение.

Answer 5

Я использовал свой код в качестве хранимой процедуры (функции), которая будет работать для извлечения любого числа, построенного из цифр в одном блоке. Это часть моей более широкой библиотеки.

DELIMITER $$

--  2013.04 [e-mail]
--  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$$

Answer 6

Если вы хотите вернуть часть строки:

SELECT id , substring(columnName,(locate('partOfString',columnName)),10) from tableName;

Locate() вернет начальную позицию соответствующей строки, которая становится начальной позицией Function Substring()

Answer 7

Я знаю, что прошло довольно много времени с тех пор, как этот вопрос был задан, но натолкнулся на него и подумал, что это будет хорошим испытанием для моего пользовательского заменителя регулярных выражений - см. Этот пост в блоге.

... И хорошая новость в том, что это возможно, хотя его нужно вызывать довольно много раз. Посмотрите эту онлайн-демонстрацию 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;