Jak zastąpić Wyrażenie regularne w MySQL?

Nie.

Ale jeśli masz dostęp do serwera, możesz użyć funkcji zdefiniowanej przez użytkownika (UDF), takiej jak mysql-UDF-regexp.

EDIT: MySQL 8.0 + możesz użyć natywnie REGEXP_REPLACE. Więcej w odpowiedzi powyżej

Zamiast tego użyj MariaDB. Posiada funkcję

REGEXP_REPLACE(col, regexp, replace)

Zobacz MariaDB docs i ulepszenia wyrażeń regularnych PCRE

Zauważ, że możesz również użyć grupowania regexp (uznałem to za bardzo przydatne):

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

Zwraca

over - stack - flow

Moja metoda brute force, aby to zadziałało, była po prostu:

1. Dump the table - mysqldump -u user -p database table > dump.sql
2. znajdź i zamień kilka wzorców - find /path/to/dump.sql -type f -exec sed -i 's/old_string/new_string/g' {} \;, są oczywiście inne wyrażenia regeularne Perla, które możesz wykonać również w pliku.
3. Import tabeli - mysqlimport -u user -p database table < dump.sql

Jeśli chcesz się upewnić, że łańcuch nie znajduje się gdzie indziej w zbiorze danych, uruchom kilka wyrażeń regularnych, aby upewnić się, że wszystkie występują w podobnym środowisku. Nie jest też tak trudno stworzyć kopię zapasową przed uruchomieniem wymiany, w przypadku, gdy przypadkowo zniszczyć coś, co traci głębię informacji.

Ostatnio napisałem funkcję MySQL do zastępowania łańcuchów za pomocą wyrażeń regularnych. Możesz znaleźć mój post w następującej lokalizacji:

Http://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/

Oto kod funkcji:

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 ;

Przykładowe wykonanie:

mysql> select regex_replace('[^a-zA-Z0-9\-]','','2my test3_text-to. check \\ my- sql (regular) ,expressions ._,');

Rozwiązujemy ten problem bez użycia regex to zapytanie zastępuje tylko dokładny łańcuch dopasowania.

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

Przykład:

Emp_id_firstname

1 jay

2 jay ajay

3 jay

Po wykonaniu wyniku zapytania:

Emp_id_firstname

1 abc

2 abc ajay

3 abc

Z przyjemnością informuję, że skoro zadano to pytanie, teraz jest zadowalająca odpowiedź! Spójrz na ten wspaniały pakiet:

Https://github.com/mysqludf/lib_mysqludf_preg

Przykładowy SQL:

SELECT PREG_REPLACE('/(.*?)(fox)/' , 'dog' , 'the quick brown fox' ) AS demo;

Znalazłem paczkę z tego posta na blogu jako linked on this question .

UPDATE 2: użyteczny zestaw funkcji regex, w tym REGEXP_REPLACE , został udostępniony w MySQL 8.0. Powoduje to niepotrzebne czytanie, chyba że jesteś ograniczony do używania wcześniejszej wersji.

UPDATE 1: mają teraz to w blogu post: http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html

Następująca rozbudowa funkcji dostarczonej przez Rasika Godawatte ale przeszukiwanie wszystkich niezbędnych podciągów, a nie tylko testowanie pojedynczych znaków:

-- ------------------------------------------------------------------------------------
-- 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 ;

Demo

Rextester Demo

Ograniczenia

1. ta metoda oczywiście zajmie chwilę, gdy podmiot ciąg jest duży. Aktualizacja: dodano minimalne i maksymalne parametry długości dopasowania dla poprawy wydajności, gdy są one znane (zero = nieznany/nieograniczony).
2. to nie pozwoli zastępowanie backreferencji (np. \1, \2 itd.) w celu zastąpienia grup przechwytywania. Jeśli ta funkcja jest potrzebna, zobacz tę odpowiedź , która próbuje zapewnić obejście poprzez aktualizację funkcji, aby umożliwić wtórne wyszukiwanie i zastępowanie w każdym znalezionym dopasowaniu (kosztem zwiększonej złożoności).
3. Jeśli ^ i / lub $ są używane we wzorze, muszą znajdować się odpowiednio na samym początku i na samym końcu-np. wzory takie jak (^start|end$) nie są obsługiwane.
4. istnieje znacznik "chciwy", aby określić, czy ogólne dopasowanie powinno być chciwe, czy nie. Łączenie chciwych i leniwych dopasowań w ramach jednego wyrażenia regularnego (np. a.*?b.*) nie jest obsługiwane.

Przykłady Użycia

Funkcja została użyta do odpowiedzi na następujące pytania Stoskoverflow:

• jak liczyć słowa w MySQL / Wyrażenie regularne zastępcę?
• jak wyodrębnić N-te słowo i policzyć wystąpienia słów w MySQL sznurek?
• jak wyodrębnić dwie kolejne cyfry z pola tekstowego w MySQL?
• jak usunąć wszystkie Nie-alfanumeryczne znaki z łańcucha w MySQL?
• jak zastąpić każdą inną instancję danego znaku w MySQL sznurek?

Możesz to zrobić ... ale to nie jest zbyt mądre ... to jest tak śmiałe, jak się postaram ... o ile pełna obsługa RegEx jest znacznie lepsza przy użyciu Perla lub tym podobnych.

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

Możemy użyć warunku IF W zapytaniu SELECT jak poniżej:

Załóżmy, że dla czegokolwiek z "ABC", "ABC1", "ABC2", "ABC3",..., chcemy zamienić na "ABC", a następnie użyć warunku REGEXP I IF() W zapytaniu SELECT, możemy to osiągnąć.

Składnia:

SELECT IF(column_name REGEXP 'ABC[0-9]$','ABC',column_name)
FROM table1 
WHERE column_name LIKE 'ABC%';

Przykład:

SELECT IF('ABC1' REGEXP 'ABC[0-9]$','ABC','ABC1');