Как заменить регулярное выражение в MySQL?

У меня есть таблица с ~ 500 тыс. Строк; varchar (255) filename столбца UTF8 содержит имя файла;

Я пытаюсь вычеркнуть различные странные символы из имени файла – думал, что буду использовать класс символов: [^a-zA-Z0-9()_ .\-]

Теперь, есть функция в MySQL, которая позволяет вам заменять регулярное выражение ? Я ищу аналогичную функциональность для функции REPLACE () – упрощен пример:

 SELECT REPLACE('stackowerflow', 'ower', 'over'); Output: "stackoverflow" /* does something like this exist? */ SELECT X_REG_REPLACE('Stackoverflow','/[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=... ", но это похоже на медленный и уродливый взломать последний курорт)

Нет.

Но если у вас есть доступ к вашему серверу, вы можете использовать определенную пользователем функцию (UDF), такую ​​как mysql-udf-regexp .

Вместо этого используйте MariaDB. Он имеет функцию

 REGEXP_REPLACE(col, regexp, replace) 

См. Документы MariaDB и PCRE. Регулярные выражения

Обратите внимание, что вы также можете использовать группировку regexp (я нашел это очень полезным):

 SELECT REGEXP_REPLACE("stackoverflow", "(stack)(over)(flow)", '\\2 - \\1 - \\3') 

возвращается

 over - stack - flow 

Мой метод грубой силы, чтобы заставить это работать, было просто:

  1. Дамп таблицы – mysqldump -u user -p database table > dump.sql
  2. Найдите и замените пару шаблонов – find /path/to/dump.sql -type f -exec sed -i 's/old_string/new_string/g' {} \; , Есть, очевидно, другие выражения perl regeular, которые вы могли бы выполнить и в файле.
  3. Импортировать таблицу – 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 ._,'); 

мы решим эту проблему, не используя regex, этот запрос заменяет только точную строку соответствия.

 update employee set employee_firstname = trim(REPLACE(concat(" ",employee_firstname," "),' jay ',' abc ')) 

Пример:

emp_id employee_firstname

1 jay

2 jay ajay

3 jay

После выполнения результата запроса:

emp_id employee_firstname

1 abc

2 abc ajay

3 abc

Я рад сообщить, что, поскольку этот вопрос был задан, теперь есть удовлетворительный ответ! Взгляните на этот потрясающий пакет:

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[[:>:]]' 

Следующее расширяет функцию, предоставленную Расикой Годаватте, но тратит через все необходимые подстроки, а не просто проверяет одиночные символы:

 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 (ie pattern ends with $ but doesn't start with ^): Set start position -- 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// 

демонстрация

Демо- версия демонстрационного примера или скрипта SQL Fiddle

Ограничения

  1. Этот метод, конечно, займет некоторое время, когда предметная строка будет большой. Обновление: теперь добавлены параметры минимальной и максимальной длины совпадений для повышения эффективности, если они известны (ноль = неизвестно / без ограничений).
  2. Это не позволит заменить обратные ссылки (например, \1 , \2 и т. Д.), Чтобы заменить группы захвата. Если эта функциональность необходима, см. Этот ответ, который пытается обеспечить обходной путь путем обновления функции, позволяющей вторичную нахождение и замену в каждом найденном совпадении (за счет повышенной сложности).
  3. Если в шаблоне используются ^ и / или $ , они должны быть в самом начале и в самом конце соответственно – например, шаблоны, такие как (^start|end$) , не поддерживаются.
  4. Существует «жадный» флаг, чтобы указать, должно ли общее сопоставление быть жадным или не жадным. Объединение жадного и ленивого соответствия в пределах одного регулярного выражения (например,. a.*?b.* ) Не поддерживается.

Мы можем использовать условие 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');