Question Comment remplacer une expression régulière dans MySQL?


J'ai une table avec environ 500k lignes; colonne varchar (255) UTF8 filename contient un nom de fichier;

J'essaie de supprimer divers caractères étranges du nom de fichier - je pensais utiliser une classe de caractères: [^a-zA-Z0-9()_ .\-]

À présent, existe-t-il une fonction dans MySQL qui vous permet de remplacer par une expression régulière? Je recherche une fonctionnalité similaire à la fonction REPLACE () - l'exemple simplifié suit:

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

Output: "stackoverflow"

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

Output: "-tackover-low"

Je sais à propos de REGEXP / RLIKE, mais ceux-là ne font que vérifier si il y a un match, pas quelle le match est.

(JE pourrait fait une "SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]'"à partir d'un script PHP, faites un preg_replace et alors "UPDATE foo ... WHERE pkey_id=...", mais cela ressemble à un piratage lent et laid de dernier recours)


424
2018-06-12 14:08


origine


Réponses:


MySQL 8.0+ vous pouvez utiliser nativement REGEXP_REPLACE.

12.5.2 Expressions régulières:

REGEXP_REPLACE (expr, pat, repl [, pos [, occurrence [, type de correspondance]]])

Remplace les occurrences de la chaîne expr correspondant à l'expression régulière spécifiée par le motif pat avec la chaîne de remplacement repl, et renvoie la chaîne résultante. Si expr, pat ou repl est NULL, la valeur de retour est NULL.

et Prise en charge d'expressions régulières:

Auparavant, MySQL utilisait la bibliothèque d'expressions rationnelles Henry Spencer pour prendre en charge les opérateurs d'expressions régulières (REGEXP, RLIKE).

La prise en charge des expressions régulières a été réimplémentée à l'aide d'ICU (International Components for Unicode), qui fournit un support Unicode complet et est sécurisé à plusieurs octets. La fonction REGEXP_LIKE () effectue une correspondance d'expression régulière à la manière des opérateurs REGEXP et RLIKE, qui sont maintenant des synonymes pour cette fonction. En outre, les fonctions REGEXP_INSTR (), REGEXP_REPLACE () et REGEXP_SUBSTR () sont disponibles pour rechercher des positions de correspondance et effectuer respectivement une substitution et une extraction de sous-chaîne.

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

DBFiddle Demo


11
2018-04-19 16:07



Non.

Mais si vous avez accès à votre serveur, vous pouvez utiliser une fonction définie par l'utilisateur (UDF) comme mysql-udf-regexp.

MODIFIER: MySQL 8.0+ vous pouvez utiliser nativement REGEXP_REPLACE. Plus dans la réponse ci-dessus


134
2018-06-12 14:16



Utilisez plutôt MariaDB. Il a une fonction

REGEXP_REPLACE(col, regexp, replace)

Voir MariaDB docs et PCRE Améliorations de l'expression régulière

Notez que vous pouvez également utiliser le regroupement regexp (j'ai trouvé cela très utile):

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

résultats

over - stack - flow

111
2017-10-03 13:11



Ma méthode de force brute pour que cela fonctionne est juste:

  1. Vider la table - mysqldump -u user -p database table > dump.sql
  2. Trouver et remplacer quelques modèles - find /path/to/dump.sql -type f -exec sed -i 's/old_string/new_string/g' {} \;Il y a évidemment d'autres expressions perl regeular que vous pouvez utiliser sur le fichier.
  3. Importer la table - mysqlimport -u user -p database table < dump.sql

Si vous voulez vous assurer que la chaîne n'est pas ailleurs dans votre jeu de données, exécutez quelques expressions régulières pour vous assurer qu'elles se produisent toutes dans un environnement similaire. Il n'est également pas si difficile de créer une sauvegarde avant d'exécuter un remplacement, au cas où vous détruisez accidentellement quelque chose qui perd de la profondeur de l'information.


100
2018-02-26 19:52



J'ai récemment écrit une fonction MySQL pour remplacer des chaînes utilisant des expressions régulières. Vous pouvez trouver mon message à l’emplacement suivant:

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

Voici le code de la fonction:

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 ;

Exemple d'exécution:

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

39
2018-06-02 15:16



nous résolvons ce problème sans utiliser regex cette requête remplace uniquement la chaîne de correspondance exacte.

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

Exemple:

emp_id employé_firstname

1 geai

2 jay ajay

3 geais

Après l'exécution du résultat de la requête:

emp_id employé_firstname

1 abc

2 abc ajay

3 abc


28
2017-12-19 05:07



Je suis heureux de signaler que depuis que cette question a été posée, il y a maintenant une réponse satisfaisante! Jetez un coup d'œil à ce formidable package:

https://github.com/mysqludf/lib_mysqludf_preg

Exemple de SQL:

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

J'ai trouvé le paquet de ce blog comme lié sur cette question.


13
2017-11-13 14:51



Vous pouvez le faire ... mais ce n'est pas très sage ... c'est à peu près aussi audacieux que je vais essayer ... dans la mesure où les RegEx supportent votre bien mieux en utilisant perl ou similaire.

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

7
2017-09-28 03:09



METTRE À JOUR: Ont maintenant fait cela dans un article de blog: http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html


Ce qui suit se développe sur le fonction fournie par Rasika Godawatte mais passe au travers de toutes les sous-chaînes nécessaires plutôt que de tester des caractères uniques:

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

Démo

Rextester Demo

Limites

  1. Cette méthode va bien sûr prendre un certain temps lorsque le sujet la chaîne est grande. Mettre à jour: Ont maintenant ajouté des paramètres de longueur de correspondance minimale et maximale pour améliorer l'efficacité lorsque ceux-ci sont connus (zéro = inconnu / illimité).

7
2017-07-29 13:49



Nous pouvons utiliser la condition IF dans la requête SELECT comme ci-dessous:

Supposons que pour quelque chose avec "ABC", "ABC1", "ABC2", "ABC3", ..., nous voulons remplacer par "ABC", puis en utilisant REGEXP et IF () condition dans la requête SELECT, nous pouvons y parvenir .

Syntaxe:

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

Exemple:

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

4
2017-12-01 06:37