¿Cómo hacer una expresión regular reemplazar en MySQL?

Tengo una tabla con ~ 500k filas; varchar (255) El filename columna UTF8 contiene un nombre de archivo;

Estoy tratando de eliminar varios caracteres extraños del nombre del archivo, pensé que usaría una clase de caracteres: [^a-zA-Z0-9()_ .\-]

Ahora, ¿hay alguna función en MySQL que te permita reemplazar a través de una expresión regular ? Estoy buscando una funcionalidad similar a la función REPLACE () – ejemplo simplificado a continuación:

 SELECT REPLACE('stackowerflow', 'ower', 'over'); Output: "stackoverflow" /* does something like this exist? */ SELECT X_REG_REPLACE('Stackoverflow','/[A-Zf]/','-'); Output: "-tackover-low" 

Sé sobre REGEXP / RLIKE , pero esos solo verifican si hay una coincidencia, no cuál es la coincidencia.

( Podría hacer un ” SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]' ” desde un script PHP, hacer un preg_replace y luego ” UPDATE foo ... WHERE pkey_id=... “, pero eso parece un truco lento y feo de último recurso)

MySQL 8.0+ puede usar nativamente REGEXP_REPLACE .

12.5.2 Expresiones regulares :

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

Reemplaza las ocurrencias en la cadena expr que coinciden con la expresión regular especificada por el patrón pat con la cadena de reemplazo repl, y devuelve la cadena resultante. Si expr, pat o repl es NULL, el valor de retorno es NULL.

y soporte de expresión regular :

Anteriormente, MySQL utilizaba la biblioteca de expresiones regulares de Henry Spencer para admitir operadores de expresiones regulares (REGEXP, RLIKE).

El soporte de expresiones regulares se ha vuelto a implementar utilizando International Components for Unicode (ICU), que brinda soporte total de Unicode y es seguro para varios discos. La función REGEXP_LIKE () realiza la coincidencia de expresiones regulares a la manera de los operadores REGEXP y RLIKE, que ahora son sinónimos de esa función. Además, las funciones REGEXP_INSTR (), REGEXP_REPLACE () y REGEXP_SUBSTR () están disponibles para buscar posiciones de coincidencia y realizar la sustitución y extracción de subcadenas, respectivamente.

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

DBFiddle Demo

No.

Pero si tiene acceso a su servidor, puede usar una función definida por el usuario (UDF) como mysql-udf-regexp .

EDITAR: MySQL 8.0+ puede usar de forma nativa REGEXP_REPLACE. Más en respuesta arriba

Use MariaDB en su lugar. Tiene una función

 REGEXP_REPLACE(col, regexp, replace) 

Ver documentos de MariaDB y PCRE Mejoras en la expresión regular

Tenga en cuenta que también puede usar la agrupación de expresiones regulares (lo encontré muy útil):

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

devoluciones

 over - stack - flow 

Mi método de fuerza bruta para hacer que esto funcione fue simplemente:

  1. mysqldump -u user -p database table > dump.sql la tabla – mysqldump -u user -p database table > dump.sql
  2. Encuentre y reemplace un par de patrones – find /path/to/dump.sql -type f -exec sed -i 's/old_string/new_string/g' {} \; , Obviamente, hay otras expresiones regulares perl que podría realizar en el archivo.
  3. Importe la tabla – mysqlimport -u user -p database table < dump.sql

Si quiere asegurarse de que la cadena no está en otro lugar en su conjunto de datos, ejecute algunas expresiones regulares para asegurarse de que todas ocurran en un entorno similar. Tampoco es tan difícil crear una copia de seguridad antes de ejecutar un reemplazo, en caso de que accidentalmente destruya algo que pierde profundidad de información.

Recientemente escribí una función MySQL para reemplazar cadenas usando expresiones regulares. Podrías encontrar mi publicación en la siguiente ubicación:

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

Aquí está el código de función:

 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 ; 

Ejemplo de ejecución:

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

resolvemos este problema sin usar regex esta consulta reemplazamos solo cadena de coincidencia exacta.

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

Ejemplo:

emp_id employee_firstname

1 jay

2 jay ajay

3 jay

Después de ejecutar el resultado de la consulta:

emp_id employee_firstname

1 ac

2 abc ajay

3 abc

Me complace informarles que desde que se hizo esta pregunta, ¡ahora hay una respuesta satisfactoria! Eche un vistazo a este fabuloso paquete:

https://github.com/mysqludf/lib_mysqludf_preg

Ejemplo de SQL:

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

El paquete de esta publicación de blog me pareció vinculado a esta pregunta .

ACTUALIZACIÓN 2: ahora se ha proporcionado un conjunto útil de funciones de expresiones regulares que incluyen REGEXP_REPLACE en MySQL 8.0. Esto hace que la lectura sea innecesaria a menos que esté obligado a usar una versión anterior.


ACTUALIZACIÓN 1: He convertido esto en una publicación de blog: http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html


A continuación, se amplía la función proporcionada por Rasika Godawatte, pero rastrea todas las subcadenas necesarias en lugar de simplemente probar caracteres individuales:

 -- ------------------------------------------------------------------------------------ -- USAGE -- ------------------------------------------------------------------------------------ -- SELECT reg_replace(, -- , -- , -- , -- , -- ); -- where: --  is the string to look in for doing the replacements --  is the regular expression to match against --  is the replacement string --  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 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 (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 ; 

Manifestación

Rextester Demo

Limitaciones

  1. Este método, por supuesto, va a tomar un tiempo cuando la secuencia del tema es grande. Actualización: ahora se han agregado los parámetros de duración de coincidencia mínima y máxima para una mejor eficiencia cuando se conocen (cero = desconocido / ilimitado).
  2. No permitirá la sustitución de referencias posteriores (por ejemplo, \1 , \2 etc.) para reemplazar los grupos de captura. Si esta funcionalidad es necesaria, consulte esta respuesta que intenta proporcionar una solución al actualizar la función para permitir un descubrimiento y reemplazo secundarios dentro de cada coincidencia encontrada (a expensas de una mayor complejidad).
  3. Si se usan ^ y / o $ en el patrón, deben estar al principio y al final, respectivamente; por ejemplo, patrones como (^start|end$) no son compatibles.
  4. Hay una bandera “codiciosa” para especificar si la concordancia general debe ser codiciosa o no codiciosa. No se admite la combinación de concordancia codiciosa y diferida dentro de una única expresión regular (por ejemplo, a.*?b.* ).

Ejemplos de uso

La función se ha utilizado para responder las siguientes preguntas de StackOverflow:

  • Cómo contar palabras en MySQL / regular expression replacer?
  • ¿Cómo extraer la palabra enésima y contar las ocurrencias de palabras en una cadena MySQL?
  • ¿Cómo extraer dos dígitos consecutivos de un campo de texto en MySQL?
  • ¿Cómo eliminar todos los caracteres no alfanuméricos de una cadena en MySQL?
  • ¿Cómo reemplazar cualquier otra instancia de un personaje en particular en una cadena de MySQL?

No puedes ‘hacerlo’ … pero no es muy sabio … es tan atrevido como lo intentaré … en lo que respecta al RegEx completo, es mucho mejor que utilices perl o algo por el estilo.

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

Podemos usar la condición IF en la consulta SELECT de la siguiente manera:

Supongamos que para cualquier cosa con “ABC”, “ABC1”, “ABC2”, “ABC3”, …, queremos reemplazar con “ABC” y luego usar la condición REGEXP e IF () en la consulta SELECT, podemos lograr esto .

Sintaxis:

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

Ejemplo:

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