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

Wenn Sie MariaDB oder MySQL 8.0 verwenden, haben diese eine Funktion

REGEXP_REPLACE(col, regexp, replace)

Ver MariaDB-Dokumente y PCRE Erweiterungen für reguläre Ausdrücke

Beachten Sie, dass Sie auch Regexp-Gruppierungen verwenden können (ich fand das sehr nützlich):

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

gibt zurück.

over - stack - flow

Answer 2

MySQL 8.0+ :

Sie können die native REGEXP_REPLACE Funktion.

Ältere Versionen:

Sie können eine benutzerdefinierte Funktion verwenden ( UDF ) wie mysql-udf-regexp .

Answer 3

Mit MySQL 8.0+ können Sie nativ verwenden REGEXP_REPLACE Funktion.

12.5.2 Reguläre Ausdrücke :

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

Ersetzt Vorkommen in der Zeichenkette expr die mit dem regulären Ausdruck übereinstimmen, der mit dem Muster pat mit der Ersetzungszeichenfolge Replik und gibt die resultierende Zeichenkette zurück. Wenn expr , pat , oder Replik ist NULL ist der Rückgabewert NULL .

und Unterstützung regulärer Ausdrücke :

Zuvor, MySQL die Bibliothek für reguläre Ausdrücke von Henry Spencer verwendet, um die Operatoren für reguläre Ausdrücke ( REGEXP , RLIKE ).

Die Unterstützung für reguläre Ausdrücke wurde mit Hilfe von International Components for Unicode (ICU) neu implementiert, das volle Unicode-Unterstützung bietet und multibyte-sicher ist. Die REGEXP_LIKE() Funktion führt den Abgleich mit regulären Ausdrücken in der Art der Funktion REGEXP y RLIKE Operatoren, die nun Synonyme für diese Funktion sind. Darüber hinaus ist die REGEXP_INSTR() , REGEXP_REPLACE() und REGEXP_SUBSTR() stehen Funktionen zur Verfügung, um Übereinstimmungspositionen zu finden und Teilstringsubstitutionen bzw. -extraktionen durchzuführen.

SELECT REGEXP_REPLACE('Stackoverflow','[A-Zf]','-',1,0,'c'); 
-- Output:
-tackover-low

DBFiddle-Demo

Answer 4

Meine Brute-Force-Methode, um dies zum Laufen zu bringen, war einfach:

1. Die Tabelle löschen - mysqldump -u user -p database table > dump.sql
2. Ein paar Muster suchen und ersetzen - find /path/to/dump.sql -type f -exec sed -i 's/old_string/new_string/g' {} \; Es gibt natürlich auch andere Perl-Regelausdrücke, die Sie auf die Datei anwenden können.
3. Importieren Sie die Tabelle - mysqlimport -u user -p database table < dump.sql

Wenn Sie sicherstellen wollen, dass die Zeichenfolge nicht an anderer Stelle in Ihrem Datensatz vorkommt, führen Sie einige reguläre Ausdrücke aus, um sicherzustellen, dass sie alle in einer ähnlichen Umgebung vorkommen. Es ist auch nicht schwer, vor dem Ersetzen eine Sicherungskopie zu erstellen, für den Fall, dass Sie versehentlich etwas zerstören, das zu einem Informationsverlust führt.

Answer 5

Ich habe vor kurzem eine MySQL-Funktion zum Ersetzen von Zeichenketten mit regulären Ausdrücken geschrieben. Sie können meinen Beitrag an der folgenden Stelle finden:

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

Hier ist der Funktionscode:

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 ;

Beispielhafte Ausführung:

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