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

MySQL 8.0+ vous pouvez utiliser nativement REGEXP_REPLACE .

12.5.2 Expressions régulières :

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

Remplace les occurrences de la chaîne expr qui correspondent à l’expression régulière spécifiée par le modèle 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 support d’expression régulière :

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

La prise en charge des expressions régulières a été réimplémentée à l’aide de International Components for Unicode (ICU), qui assure une prise en charge complète d’Unicode et est sûre sur plusieurs octets. La fonction REGEXP_LIKE () effectue une correspondance des expressions régulières à 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

Non.

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

EDIT: MySQL 8.0+ vous pouvez utiliser nativement REGEXP_REPLACE. Plus en réponse ci-dessus

Utilisez plutôt MariaDB. Il a une fonction

 REGEXP_REPLACE(col, regexp, replace) 

Voir les documents MariaDB et les améliorations de l’expression régulière PCRE

Notez que vous pouvez également utiliser le regroupement d’expressions rationnelles (j’ai trouvé cela très utile):

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

résultats

 over - stack - flow 

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

1. Vidage de la table – mysqldump -u user -p database table > dump.sql
2. Trouvez et remplacez un couple de motifs – find /path/to/dump.sql -type f -exec sed -i 's/old_ssortingng/new_ssortingng/g' {} \; Il existe évidemment d’autres expressions perl regeular que vous pouvez également 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 pas difficile non plus de créer une sauvegarde avant de procéder à un remplacement, au cas où vous détruiriez accidentellement quelque chose qui perd de la profondeur.

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 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 ._,'); 

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

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

Exemple:

emp_id employé_firstname

1 geai

2 geai ajay

3 geais

Après avoir exécuté le résultat de la requête:

emp_id employé_firstname

1 abc

2 abc ajay

3 abc

Je suis heureux d’annoncer 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 package de cet article de blog lié à cette question .

UPDATE 2: Un ensemble utile de fonctions regex, y compris REGEXP_REPLACE, a été fourni en MySQL 8.0. Cela rend la lecture inutile, sauf si vous êtes contraint d’utiliser une version antérieure.

MISE À JOUR 1: Maintenant, faites cela dans un article de blog: http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html

Ce qui suit développe la fonction fournie par Rasika Godawatte, mais passe en revue toutes les sous-chaînes nécessaires plutôt que de tester des caractères uniques:

 -- ------------------------------------------------------------------------------------ -- USAGE -- ------------------------------------------------------------------------------------ -- SELECT reg_replace(, -- , -- , -- , -- , -- ); -- where: --  is the ssortingng to look in for doing the replacements --  is the regular expression to match against --  is the replacement ssortingng --  is TRUE for greedy matching or FALSE for non-greedy matching --  specifies the minimum match length --  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 ssortingng rather than part of a ssortingng 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 (ie 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 la chaîne de sujet est grande. Mise à jour: vous avez maintenant ajouté des parameters de longueur de correspondance minimale et maximale pour améliorer l’efficacité lorsque ceux-ci sont connus (zéro = inconnu / illimité).
2. Cela ne permettra pas la substitution de backreferences (par exemple \1 , \2 etc.) pour remplacer les groupes de capture. Si cette fonctionnalité est nécessaire, veuillez consulter cette réponse qui tente de fournir une solution de contournement en mettant à jour la fonction pour autoriser une recherche et un remplacement secondaire dans chaque correspondance trouvée (au désortingment de la complexité).
3. Si ^ et / ou $ sont utilisés dans le pattern, ils doivent être au tout début et à la toute fin – par exemple, des patterns tels que (^start|end$) ne sont pas supportés.
4. Il existe un indicateur “gourmand” pour indiquer si la correspondance globale doit être gourmande ou non gourmande. La combinaison d’une appariement gourmand et paresseux au sein d’une seule expression régulière (par exemple, a.*?b.* ) N’est pas prise en charge.

Exemples d’utilisation

La fonction a été utilisée pour répondre aux questions suivantes concernant StackOverflow:

• Comment compter les mots dans MySQL / replacer l’expression régulière?
• Comment extraire le nième mot et compter les occurrences de mots dans une chaîne MySQL?
• Comment extraire deux chiffres consécutifs d’un champ de texte dans MySQL?
• Comment supprimer tous les caractères non alpha d’une chaîne dans MySQL?
• Comment remplacer toute autre instance d’un caractère particulier dans une chaîne MySQL?

Vous pouvez le faire … mais ce n’est pas très sage … c’est à peu près aussi audacieux que je vais essayer … autant que RegEx complet supporte 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[[:>:]]' 

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 utiliser les conditions REGEXP et IF () dans la requête SELECT, nous pouvons y arriver .

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