Как сделать регулярное выражение заменить в MySQL?
У меня есть таблица с ~500k строк; varchar(255) колонка UTF8 filename содержит имя файла;
Я пытаюсь удалить различные странные символы из имени файла - думал, что я буду использовать класс символов: [^a-zA-Z0-9()_ .\-]
Теперь, есть ли в MySQL функция, которая позволяет заменять регулярное выражение? Я ищу аналогичную функциональность функции REPLACE() - упрощенный пример приведен ниже:
SELECT REPLACE('stackowerflow', 'ower', 'over');
Output: "stackru"
/* does something like this exist? */
SELECT X_REG_REPLACE('Stackru','/[A-Zf]/','-');
Output: "-tackover-low"
Я знаю о REGEXP / RLIKE, но они проверяют только совпадение, а не совпадение.
(Я мог бы сделать " SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]' "из сценария PHP, сделайте preg_replace а потом " UPDATE foo ... WHERE pkey_id=... ", но это выглядит как последний медленный и уродливый взлом)
11 ответов
MySQL 8.0+ вы можете использовать изначально REGEXP_REPLACE,
REGEXP_REPLACE (expr, pat, repl [, pos [, вхождение [, match_type]]])
Заменяет вхождения в строке expr, которые соответствуют регулярному выражению, указанному в шаблоне pat, на строку замены repl и возвращает результирующую строку. Если expr, pat или repl имеют значение NULL, возвращаемое значение равно NULL.
и поддержка регулярных выражений:
Ранее MySQL использовал библиотеку регулярных выражений Генри Спенсера для поддержки операторов регулярных выражений (REGEXP, RLIKE).
Поддержка регулярных выражений была переопределена с использованием International Components for Unicode (ICU), которая обеспечивает полную поддержку Unicode и является многобайтовой безопасностью. Функция REGEXP_LIKE() выполняет сопоставление регулярных выражений в виде операторов REGEXP и RLIKE, которые теперь являются синонимами для этой функции. Кроме того, доступны функции REGEXP_INSTR(), REGEXP_REPLACE() и REGEXP_SUBSTR() для поиска позиций совпадения и выполнения подстановки и извлечения подстроки соответственно.
SELECT REGEXP_REPLACE('Stackru','[A-Zf]','-',1,0,'c');
-- Output:
-tackover-low
Вместо этого используйте MariaDB. Имеет функцию
REGEXP_REPLACE(col, regexp, replace)
См. MariaDB документы и PCRE Улучшения регулярных выражений
Обратите внимание, что вы также можете использовать группировку регулярных выражений (я нашел это очень полезным):
SELECT REGEXP_REPLACE("stackru", "(stack)(over)(flow)", '\\2 - \\1 - \\3')
возвращается
over - stack - flow
Нет.
Но если у вас есть доступ к вашему серверу, вы можете использовать пользовательскую функцию (UDF), например mysql-udf-regexp.
РЕДАКТИРОВАТЬ: MySQL 8.0+ вы можете использовать изначально REGEXP_REPLACE. Больше в ответе выше
Мой метод грубой силы, чтобы заставить это работать, был просто:
- Дамп таблицы -
mysqldump -u user -p database table > dump.sql - Найти и заменить пару моделей -
find /path/to/dump.sql -type f -exec sed -i 's/old_string/new_string/g' {} \;, Очевидно, есть и другие регулярные выражения perl, которые вы можете выполнить с файлом. - Импортировать таблицу -
mysqlimport -u user -p database table < dump.sql
Если вы хотите убедиться, что строка не находится в другом месте вашего набора данных, запустите несколько регулярных выражений, чтобы убедиться, что все они встречаются в похожей среде. Также не так сложно создать резервную копию перед выполнением замены на случай, если вы случайно уничтожите что-то, что приведет к потере информации.
Недавно я написал функцию MySQL для замены строк с помощью регулярных выражений. Вы можете найти мой пост в следующем месте:
http://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/
Вот код функции:
DELIMITER $$
CREATE FUNCTION `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
DECLARE temp VARCHAR(1000);
DECLARE ch VARCHAR(1);
DECLARE i INT;
SET i = 1;
SET temp = '';
IF original REGEXP pattern THEN
loop_label: LOOP
IF i>CHAR_LENGTH(original) THEN
LEAVE loop_label;
END IF;
SET ch = SUBSTRING(original,i,1);
IF NOT ch REGEXP pattern THEN
SET temp = CONCAT(temp,ch);
ELSE
SET temp = CONCAT(temp,replacement);
END IF;
SET i=i+1;
END LOOP;
ELSE
SET temp = original;
END IF;
RETURN temp;
END$$
DELIMITER ;
Пример выполнения:
mysql> select regex_replace('[^a-zA-Z0-9\-]','','2my test3_text-to. check \\ my- sql (regular) ,expressions ._,');
Мы решаем эту проблему, не используя регулярные выражения, этот запрос заменяет только строку с точным соответствием.
update employee set
employee_firstname =
trim(REPLACE(concat(" ",employee_firstname," "),' jay ',' abc '))
Пример:
emp_id имя_служащего
1 сойка
2 сойки
3 сойки
После выполнения запроса:
emp_id имя_служащего
1 азбука
2 ABC AJAY
3 азбуки
ОБНОВЛЕНИЕ 2: полезный набор функций регулярных выражений, включая REGEXP_REPLACE, теперь представлен в MySQL 8.0. Это делает чтение ненужным, если вы не ограничены использованием более ранней версии.
ОБНОВЛЕНИЕ 1: Теперь превратили это в сообщение в блоге: http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html
Следующее расширяет функции, предоставляемые Rasika Godawatte, но обходит все необходимые подстроки, а не просто проверяет отдельные символы:
-- ------------------------------------------------------------------------------------
-- USAGE
-- ------------------------------------------------------------------------------------
-- SELECT reg_replace(<subject>,
-- <pattern>,
-- <replacement>,
-- <greedy>,
-- <minMatchLen>,
-- <maxMatchLen>);
-- where:
-- <subject> is the string to look in for doing the replacements
-- <pattern> is the regular expression to match against
-- <replacement> is the replacement string
-- <greedy> is TRUE for greedy matching or FALSE for non-greedy matching
-- <minMatchLen> specifies the minimum match length
-- <maxMatchLen> specifies the maximum match length
-- (minMatchLen and maxMatchLen are used to improve efficiency but are
-- optional and can be set to 0 or NULL if not known/required)
-- Example:
-- SELECT reg_replace(txt, '^[Tt][^ ]* ', 'a', TRUE, 2, 0) FROM tbl;
DROP FUNCTION IF EXISTS reg_replace;
DELIMITER //
CREATE FUNCTION reg_replace(subject VARCHAR(21845), pattern VARCHAR(21845),
replacement VARCHAR(21845), greedy BOOLEAN, minMatchLen INT, maxMatchLen INT)
RETURNS VARCHAR(21845) DETERMINISTIC BEGIN
DECLARE result, subStr, usePattern VARCHAR(21845);
DECLARE startPos, prevStartPos, startInc, len, lenInc INT;
IF subject REGEXP pattern THEN
SET result = '';
-- Sanitize input parameter values
SET minMatchLen = IF(minMatchLen < 1, 1, minMatchLen);
SET maxMatchLen = IF(maxMatchLen < 1 OR maxMatchLen > CHAR_LENGTH(subject),
CHAR_LENGTH(subject), maxMatchLen);
-- Set the pattern to use to match an entire string rather than part of a string
SET usePattern = IF (LEFT(pattern, 1) = '^', pattern, CONCAT('^', pattern));
SET usePattern = IF (RIGHT(pattern, 1) = '$', usePattern, CONCAT(usePattern, '$'));
-- Set start position to 1 if pattern starts with ^ or doesn't end with $.
IF LEFT(pattern, 1) = '^' OR RIGHT(pattern, 1) <> '$' THEN
SET startPos = 1, startInc = 1;
-- Otherwise (i.e. pattern ends with $ but doesn't start with ^): Set start pos
-- to the min or max match length from the end (depending on "greedy" flag).
ELSEIF greedy THEN
SET startPos = CHAR_LENGTH(subject) - maxMatchLen + 1, startInc = 1;
ELSE
SET startPos = CHAR_LENGTH(subject) - minMatchLen + 1, startInc = -1;
END IF;
WHILE startPos >= 1 AND startPos <= CHAR_LENGTH(subject)
AND startPos + minMatchLen - 1 <= CHAR_LENGTH(subject)
AND !(LEFT(pattern, 1) = '^' AND startPos <> 1)
AND !(RIGHT(pattern, 1) = '$'
AND startPos + maxMatchLen - 1 < CHAR_LENGTH(subject)) DO
-- Set start length to maximum if matching greedily or pattern ends with $.
-- Otherwise set starting length to the minimum match length.
IF greedy OR RIGHT(pattern, 1) = '$' THEN
SET len = LEAST(CHAR_LENGTH(subject) - startPos + 1, maxMatchLen), lenInc = -1;
ELSE
SET len = minMatchLen, lenInc = 1;
END IF;
SET prevStartPos = startPos;
lenLoop: WHILE len >= 1 AND len <= maxMatchLen
AND startPos + len - 1 <= CHAR_LENGTH(subject)
AND !(RIGHT(pattern, 1) = '$'
AND startPos + len - 1 <> CHAR_LENGTH(subject)) DO
SET subStr = SUBSTRING(subject, startPos, len);
IF subStr REGEXP usePattern THEN
SET result = IF(startInc = 1,
CONCAT(result, replacement), CONCAT(replacement, result));
SET startPos = startPos + startInc * len;
LEAVE lenLoop;
END IF;
SET len = len + lenInc;
END WHILE;
IF (startPos = prevStartPos) THEN
SET result = IF(startInc = 1, CONCAT(result, SUBSTRING(subject, startPos, 1)),
CONCAT(SUBSTRING(subject, startPos, 1), result));
SET startPos = startPos + startInc;
END IF;
END WHILE;
IF startInc = 1 AND startPos <= CHAR_LENGTH(subject) THEN
SET result = CONCAT(result, RIGHT(subject, CHAR_LENGTH(subject) + 1 - startPos));
ELSEIF startInc = -1 AND startPos >= 1 THEN
SET result = CONCAT(LEFT(subject, startPos), result);
END IF;
ELSE
SET result = subject;
END IF;
RETURN result;
END//
DELIMITER ;
демонстрация
Ограничения
- Этот метод, конечно, займет некоторое время, когда строка темы велика. Обновление: теперь добавлены параметры минимальной и максимальной длины соответствия для повышения эффективности, когда они известны (ноль = неизвестно / не ограничено).
- Это не позволит замену обратных ссылок (например,
\1,\2и т.д.) заменить группы захвата. Если эта функциональность необходима, просмотрите этот ответ, в котором предпринята попытка найти обходной путь путем обновления функции, чтобы разрешить вторичный поиск и замену в каждом найденном совпадении (за счет повышения сложности). - Если
^и / или$используется в шаблоне, они должны быть в самом начале и в самом конце соответственно - например, такие шаблоны как(^start|end$)не поддерживаются - Есть флаг "жадный", чтобы указать, должно ли полное соответствие быть жадным или не жадным. Объединение жадного и ленивого соответствия в одном регулярном выражении (например,
a.*?b.*) не поддерживается.
Примеры использования
Эта функция была использована для ответа на следующие вопросы Stackru:
- Как считать слова в MySQL / заменителе регулярных выражений?
- Как извлечь n-е слово и посчитать вхождения слов в строке MySQL?
- Как извлечь две последовательные цифры из текстового поля в MySQL?
- Как удалить все не буквенно-цифровые символы из строки в MySQL?
- Как заменить каждый другой экземпляр определенного символа в строке MySQL?
Я рад сообщить, что, поскольку этот вопрос был задан, теперь есть удовлетворительный ответ! Взгляните на этот потрясающий пакет:
https://github.com/mysqludf/lib_mysqludf_preg
Пример SQL:
SELECT PREG_REPLACE('/(.*?)(fox)/' , 'dog' , 'the quick brown fox' ) AS demo;
Я нашел пакет из этого поста в блоге как связанный по этому вопросу.
Вы можете "сделать" это... но это не очень мудро... это примерно так же дерзко, как я постараюсь... настолько, насколько полный RegEx поддерживает вашу гораздо лучшую работу с использованием perl или чего-то подобного.
UPDATE db.tbl
SET column =
CASE
WHEN column REGEXP '[[:<:]]WORD_TO_REPLACE[[:>:]]'
THEN REPLACE(column,'WORD_TO_REPLACE','REPLACEMENT')
END
WHERE column REGEXP '[[:<:]]WORD_TO_REPLACE[[:>:]]'
I think there is an easy way to achieve this and It's working fine for me.
To SELECT rows using REGEX
SELECT * FROM `table_name` WHERE `column_name_to_find` REGEXP 'string-to-find'
To UPDATE rows using REGEX
UPDATE `table_name` SET column_name_to_find=REGEXP_REPLACE(column_name_to_find, 'string-to-find', 'string-to-replace') WHERE column_name_to_find REGEXP 'string-to-find'
REGEXP Reference:https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/
Мы можем использовать условие IF в запросе SELECT, как показано ниже:
Предположим, что для чего-либо с "ABC","ABC1","ABC2","ABC3",... мы хотим заменить на "ABC", а затем с помощью условий REGEXP и IF() в запросе SELECT, мы можем достичь этого,
Синтаксис:
SELECT IF(column_name REGEXP 'ABC[0-9]$','ABC',column_name)
FROM table1
WHERE column_name LIKE 'ABC%';
Пример:
SELECT IF('ABC1' REGEXP 'ABC[0-9]$','ABC','ABC1');
Нижеследующее в основном находит первое совпадение слева, а затем заменяет все его совпадения (протестировано в mysql-5.6).
Использование:
SELECT REGEX_REPLACE('dis ambiguity', 'dis[[:space:]]*ambiguity', 'disambiguity');
Реализация:
DELIMITER $$
CREATE FUNCTION REGEX_REPLACE(
var_original VARCHAR(1000),
var_pattern VARCHAR(1000),
var_replacement VARCHAR(1000)
) RETURNS
VARCHAR(1000)
COMMENT 'Based on https://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/'
BEGIN
DECLARE var_replaced VARCHAR(1000) DEFAULT var_original;
DECLARE var_leftmost_match VARCHAR(1000) DEFAULT
REGEX_CAPTURE_LEFTMOST(var_original, var_pattern);
WHILE var_leftmost_match IS NOT NULL DO
IF var_replacement <> var_leftmost_match THEN
SET var_replaced = REPLACE(var_replaced, var_leftmost_match, var_replacement);
SET var_leftmost_match = REGEX_CAPTURE_LEFTMOST(var_replaced, var_pattern);
ELSE
SET var_leftmost_match = NULL;
END IF;
END WHILE;
RETURN var_replaced;
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION REGEX_CAPTURE_LEFTMOST(
var_original VARCHAR(1000),
var_pattern VARCHAR(1000)
) RETURNS
VARCHAR(1000)
COMMENT '
Captures the leftmost substring that matches the [var_pattern]
IN [var_original], OR NULL if no match.
'
BEGIN
DECLARE var_temp_l VARCHAR(1000);
DECLARE var_temp_r VARCHAR(1000);
DECLARE var_left_trim_index INT;
DECLARE var_right_trim_index INT;
SET var_left_trim_index = 1;
SET var_right_trim_index = 1;
SET var_temp_l = '';
SET var_temp_r = '';
WHILE (CHAR_LENGTH(var_original) >= var_left_trim_index) DO
SET var_temp_l = LEFT(var_original, var_left_trim_index);
IF var_temp_l REGEXP var_pattern THEN
WHILE (CHAR_LENGTH(var_temp_l) >= var_right_trim_index) DO
SET var_temp_r = RIGHT(var_temp_l, var_right_trim_index);
IF var_temp_r REGEXP var_pattern THEN
RETURN var_temp_r;
END IF;
SET var_right_trim_index = var_right_trim_index + 1;
END WHILE;
END IF;
SET var_left_trim_index = var_left_trim_index + 1;
END WHILE;
RETURN NULL;
END $$
DELIMITER ;
Да, ты можешь.
UPDATE table_name
SET column_name = 'seach_str_name'
WHERE column_name REGEXP '[^a-zA-Z0-9()_ .\-]';