befehl - mysql replace substring in column



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

Ich freue mich, Ihnen mitteilen zu können, dass diese Frage nun gestellt wurde und nun eine zufriedenstellende Antwort gibt! Werfen Sie einen Blick auf dieses tolle 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 aus diesem Blogpost als verlinkt zu dieser Frage gefunden .

Ich habe einen Tisch mit ~ 500k Reihen; varchar (255) UTF8-Spalte filename enthält einen Dateinamen;

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

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

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

Output: "stackoverflow"

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

Output: "-tackover-low"

Ich weiß über REGEXP/RLIKE , aber diese überprüfen nur, ob es eine Übereinstimmung gibt, nicht was die Übereinstimmung ist.

(Ich könnte eine " SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]' " aus einem PHP-Skript tun, eine preg_replace und dann " UPDATE foo ... WHERE pkey_id=... ", aber das sieht wie ein langsamer und hässlicher Hack aus


Answer #1

Ich habe kürzlich eine MySQL-Funktion geschrieben, die Strings mit regulären Ausdrücken ersetzt. Sie können meinen Beitrag am folgenden Ort 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 ;

Beispielausführung:

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

Answer #2

MySQL 8.0+ könnten Sie nativ REGEXP_REPLACE .

12.5.2 Reguläre Ausdrücke :

REGEXP_REPLACE (Ausdr, Pat, Repl [, Pos [, Vorkommen [, Match-Typ]]])

Ersetzt Vorkommen in der Zeichenfolge expr, die mit dem regulären Ausdruck übereinstimmen, der von pattern pat mit der Ersatzzeichenfolge repl angegeben wurde, und gibt die resultierende Zeichenfolge zurück. Wenn expr, pat oder repl NULL ist, ist der Rückgabewert NULL.

und Unterstützung für reguläre Ausdrücke :

Zuvor verwendete MySQL die reguläre Ausdrucksbibliothek von Henry Spencer, um reguläre Ausdrucksoperatoren (REGEXP, RLIKE) zu unterstützen.

Die Unterstützung für reguläre Ausdrücke wurde mithilfe von International Components for Unicode (ICU) neu implementiert, das vollständige Unicode-Unterstützung bietet und Multibyte-sicher ist. Die REGEXP_LIKE () - Funktion führt eine reguläre Ausdrucksübereinstimmung in der Art der REGEXP- und RLIKE-Operatoren durch, die jetzt Synonyme für diese Funktion sind. Darüber hinaus sind die Funktionen REGEXP_INSTR (), REGEXP_REPLACE () und REGEXP_SUBSTR () verfügbar, um Übereinstimmungspositionen zu finden und Substring-Substitution bzw. -Extraktion durchzuführen.


Answer #3

Nein.

Wenn Sie jedoch Zugriff auf Ihren Server haben, können Sie eine benutzerdefinierte Funktion (UDF) wie mysql-udf-regexp .

EDIT: MySQL 8.0+ könnten Sie nativ REGEXP_REPLACE verwenden. Mehr in Antwort oben


Answer #4

Verwenden Sie stattdessen MariaDB. Es hat eine Funktion

REGEXP_REPLACE(col, regexp, replace)

Siehe MariaDB-Dokumente und PCRE-Erweiterungen für reguläre Ausdrücke

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

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

kehrt zurück

over - stack - flow

Answer #5

Wir können IF-Bedingung in SELECT-Abfrage wie folgt verwenden:

Nehmen wir an, dass wir für alles mit "ABC", "ABC1", "ABC2", "ABC3", ... mit "ABC" ersetzen möchten und dann die Bedingung REGEXP und IF () in der SELECT-Abfrage verwenden können .

Syntax:

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

Beispiel:

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

Answer #6

UPDATE: Habe dies nun zu einem Blogbeitrag gemacht: http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html

Das Folgende erweitert die Funktion von Rasika Godawatte , durchforstet jedoch alle notwendigen Teilstrings und testet nicht nur einzelne Zeichen:

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

Einschränkungen

  1. Diese Methode wird natürlich eine Weile dauern, wenn die Betreffzeile groß ist. Update: Haben nun minimale und maximale Match-Länge-Parameter für eine verbesserte Effizienz hinzugefügt, wenn diese bekannt sind (Null = unbekannt / unbegrenzt).
  2. Es wird nicht erlaubt, die Rückverweise zu ersetzen (zB \1 , \2 usw.), um die einfangenden Gruppen zu ersetzen. Wenn diese Funktionalität benötigt wird, lesen Sie diese Antwort, die versucht, eine Problemumgehung bereitzustellen, indem Sie die Funktion so aktualisieren, dass innerhalb jeder gefundenen Übereinstimmung ein sekundäres Suchen und Ersetzen möglich ist (auf Kosten erhöhter Komplexität).
  3. Wenn ^ und / oder $ im Muster verwendet werden, müssen sie ganz am Anfang bzw. am Ende stehen - zB Muster wie (^start|end$) werden nicht unterstützt.
  4. Es gibt ein "gieriges" Flag, um anzugeben, ob das gesamte Matching gierig oder nicht gierig sein soll. Die Kombination von gierigem und träglichem Abgleich innerhalb eines einzelnen regulären Ausdrucks (z a.*?b.* B. a.*?b.* ) Wird nicht unterstützt.




mysql-udf