Wie kann man einen regulären Ausdruck in MySQL ersetzen?

Question

Ich habe eine Tabelle mit ~500k Zeilen; varchar(255) UTF8 Spalte filename enthält einen Dateinamen;

Ich versuche, verschiedene seltsame Zeichen aus dem Dateinamen zu entfernen - ich dachte, ich würde eine Zeichenklasse verwenden: [^a-zA-Z0-9()_ .\-]

Jetzt, Gibt es in MySQL eine Funktion, mit der Sie durch einen regulären Ausdruck ersetzen können ? Ich bin auf der Suche nach einer ähnlichen Funktionalität wie die REPLACE()-Funktion - es folgt ein vereinfachtes Beispiel:

SELECT REPLACE('stackowerflow', 'ower', 'over');

Output: "stackoverflow"

/* does something like this exist? */
SELECT X_REG_REPLACE('Stackoverflow','/[A-Zf]/','-'); 

Output: "-tackover-low"

Ich weiß von REGEXP/RLIKE aber diese prüfen nur si es gibt eine Übereinstimmung, nicht was das Spiel ist.

(I könnte eine " SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]' " von einem PHP-Skript aus, führen Sie eine preg_replace und dann " UPDATE foo ... WHERE pkey_id=... ", aber das sieht nach einem langsamen und hässlichen Hack als letzte Rettung aus)

Answer 1

Wir lösen dieses Problem ohne Verwendung von regex Diese Abfrage ersetzt nur exakt übereinstimmende Zeichenfolgen.

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

Beispiel:

emp_id mitarbeiter_vorname

1 Häher

2 Jay Ajay

3 Häher

Nach der Ausführung des Abfrageergebnisses:

emp_id mitarbeiter_vorname

1 abc

2 abc ajay

3 abc

Answer 2

UPDATE 2: Ein nützlicher Satz von Regex-Funktionen, darunter REGEXP_REPLACE wurden jetzt in MySQL 8.0 bereitgestellt. Das macht das Weiterlesen überflüssig, es sei denn, Sie sind gezwungen, eine frühere Version zu verwenden.

---

UPDATE 1: Ich habe daraus jetzt einen Blogbeitrag gemacht: http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html

---

Im Folgenden werden die folgenden Ausführungen ergänzt Funktion bereitgestellt von Rasika Godawatte durchforstet aber alle notwendigen Teilzeichenfolgen, anstatt nur einzelne Zeichen zu prüfen:

-- ------------------------------------------------------------------------------------
-- 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 IS NULL OR minMatchLen < 1, 1, minMatchLen);
    SET maxMatchLen = IF(maxMatchLen IS NULL OR 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

Beschränkungen

1. Diese Methode wird natürlich eine Weile dauern, wenn das Thema Zeichenfolge groß ist. Aktualisierung: Es wurden nun Parameter für die minimale und maximale Matchlänge hinzugefügt, um die Effizienz zu verbessern, wenn diese bekannt sind (Null = unbekannt/unbegrenzt).
2. Es wird nicht die Ersetzung von Rückverweisen (z. B. \1 , \2 usw.), um die Erfassungsgruppen zu ersetzen. Wenn diese Funktionalität benötigt wird, lesen Sie bitte diese Antwort die versucht, eine Abhilfe zu schaffen, indem sie die Funktion so aktualisiert, dass ein sekundäres Suchen und Ersetzen innerhalb jeder gefundenen Übereinstimmung möglich ist (auf Kosten einer erhöhten Komplexität).
3. Si ^ und/oder $ in dem Muster verwendet wird, müssen sie ganz am Anfang bzw. ganz am Ende stehen - z. B. Muster wie (^start|end$) werden nicht unterstützt.
4. Es gibt ein "greedy"-Flag, mit dem angegeben werden kann, ob der gesamte Abgleich greedy oder non-greedy sein soll. Die Kombination von "greedy" und "lazy" innerhalb eines einzigen regulären Ausdrucks (z.B. a.*?b.* ) wird nicht unterstützt.

Beispiele für die Verwendung

Die Funktion wurde verwendet, um die folgenden StackOverflow-Fragen zu beantworten:

• Wie zähle ich Wörter in MySQL / regulärer Ausdruck Ersetzer?
• Wie extrahiert man das n-te Wort und zählt die Wortvorkommen in einer MySQL Zeichenkette?
• Wie extrahiert man zwei aufeinanderfolgende Ziffern aus einem Textfeld in MySQL?
• Wie entfernt man alle nicht-alphanumerischen Zeichen aus einer Zeichenkette in MySQL?
• Wie ersetzt man jedes zweite Vorkommen eines bestimmten Zeichens in einer MySQL Zeichenfolge?
• Wie erhalte ich alle eindeutigen Wörter mit einer bestimmten Mindestlänge aus mehreren Spalten einer MySQL-Tabelle?

Answer 3

Ich freue mich, Ihnen mitteilen zu können, dass es auf diese Frage nun eine zufrieden stellende Antwort gibt! Werfen Sie einen Blick auf dieses großartige Paket:

https://github.com/mysqludf/lib_mysqludf_preg

Beispiel-SQL:

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

Ich habe das Paket von dieser Blogbeitrag wie verlinkt auf diese Frage .

Answer 4

Sie 'können' es tun ... aber es ist nicht sehr klug ... dies ist etwa so gewagt wie ich versuchen werde ... so weit wie volle RegEx Unterstützung Ihre viel besser mit Perl oder dergleichen.

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

Answer 5

Ich glaube, es gibt eine einfache Möglichkeit, dies zu erreichen, und bei mir funktioniert es gut.

Um Zeilen mit REGEX auszuwählen

SELECT * FROM `table_name` WHERE `column_name_to_find` REGEXP 'string-to-find'

So aktualisieren Sie Zeilen mit 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-Referenz: https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/