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 können die IF-Bedingung in der SELECT-Abfrage wie folgt verwenden:

Angenommen, wir wollen alles, was "ABC", "ABC1", "ABC2", "ABC3",... enthält, durch "ABC" ersetzen, dann können wir dies mit REGEXP und IF()-Bedingung in der SELECT-Abfrage erreichen.

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 2

Das folgende Beispiel findet grundsätzlich die erste Übereinstimmung von links und ersetzt dann alle Vorkommen dieser Übereinstimmung (getestet in mysql-5.6 ).

Verwendung:

SELECT REGEX_REPLACE('dis ambiguity', 'dis[[:space:]]*ambiguity', 'disambiguity');

Umsetzung:

DELIMITER $$
CREATE FUNCTION REGEX_REPLACE(
  var_original VARCHAR(1000),
  var_pattern VARCHAR(1000),
  var_replacement VARCHAR(1000)
  ) RETURNS
    VARCHAR(1000)
  COMMENT 'Based on https://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/'
BEGIN
  DECLARE var_replaced VARCHAR(1000) DEFAULT var_original;
  DECLARE var_leftmost_match VARCHAR(1000) DEFAULT
    REGEX_CAPTURE_LEFTMOST(var_original, var_pattern);
    WHILE var_leftmost_match IS NOT NULL DO
      IF var_replacement <> var_leftmost_match THEN
        SET var_replaced = REPLACE(var_replaced, var_leftmost_match, var_replacement);
        SET var_leftmost_match = REGEX_CAPTURE_LEFTMOST(var_replaced, var_pattern);
        ELSE
          SET var_leftmost_match = NULL;
        END IF;
      END WHILE;
  RETURN var_replaced;
END $$
DELIMITER ;

---

DELIMITER $$
CREATE FUNCTION REGEX_CAPTURE_LEFTMOST(
  var_original VARCHAR(1000),
  var_pattern VARCHAR(1000)
  ) RETURNS
    VARCHAR(1000)
  COMMENT '
  Captures the leftmost substring that matches the [var_pattern]
  IN [var_original], OR NULL if no match.
  '
BEGIN
  DECLARE var_temp_l VARCHAR(1000);
  DECLARE var_temp_r VARCHAR(1000);
  DECLARE var_left_trim_index INT;
  DECLARE var_right_trim_index INT;
  SET var_left_trim_index = 1;
  SET var_right_trim_index = 1;
  SET var_temp_l = '';
  SET var_temp_r = '';
  WHILE (CHAR_LENGTH(var_original) >= var_left_trim_index) DO
    SET var_temp_l = LEFT(var_original, var_left_trim_index);
    IF var_temp_l REGEXP var_pattern THEN
      WHILE (CHAR_LENGTH(var_temp_l) >= var_right_trim_index) DO
        SET var_temp_r = RIGHT(var_temp_l, var_right_trim_index);
        IF var_temp_r REGEXP var_pattern THEN
          RETURN var_temp_r;
          END IF;
        SET var_right_trim_index = var_right_trim_index + 1;
        END WHILE;
      END IF;
    SET var_left_trim_index = var_left_trim_index + 1;
    END WHILE;
  RETURN NULL;
END $$
DELIMITER ;

Answer 3

Ja, das können Sie.

UPDATE table_name 
  SET column_name = 'seach_str_name'
  WHERE column_name REGEXP '[^a-zA-Z0-9()_ .\-]';