mysql - regexp_replace - regexp sql
¿Cómo hacer una expresión regular reemplazar en MySQL? (10)
Tengo una tabla con ~ 500k filas; varchar (255) filename columna UTF8 contiene un nombre de archivo;
Estoy tratando de eliminar varios caracteres extraños del nombre de archivo; pensé que usaría una clase de caracteres: [^a-zA-Z0-9()_ ./-]
Ahora, ¿hay una 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 REEMPLAZAR (); a continuación se muestra un ejemplo simplificado:
SELECT REPLACE(''stackowerflow'', ''ower'', ''over'');
Output: "stackoverflow"
/* does something like this exist? */
SELECT X_REG_REPLACE(''Stackoverflow'',''/[A-Zf]/'',''-'');
Output: "-tackover-low"
Sé de REGEXP/RLIKE , pero solo comprueban 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 último recurso lento y feo hack)
Lo puedes hacer ... pero no es muy sensato ... esto es tan atrevido como lo intentaré ... en la medida en que los RegEx completos te ayuden a utilizar Perl o algo similar.
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[[:>:]]''
Me complace informar que desde que se hizo esta pregunta, ¡ahora hay una respuesta satisfactoria! Echa un vistazo a este excelente paquete:
https://github.com/mysqludf/lib_mysqludf_preg
Ejemplo de SQL:
SELECT PREG_REPLACE(''/(.*?)(fox)/'' , ''dog'' , ''the quick brown fox'' ) AS demo;
Encontré el paquete de esta publicación de blog como vinculado en esta pregunta .
Mi método de fuerza bruta para hacer que esto funcionara era simplemente:
-
mysqldump -u user -p database table > dump.sqlla tabla -mysqldump -u user -p database table > dump.sql - Busque 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 también podrías realizar en el archivo. - Importe la tabla -
mysqlimport -u user -p database table < dump.sql
Si desea asegurarse de que la cadena no se encuentra en ninguna otra parte de su conjunto de datos, ejecute algunas expresiones regulares para asegurarse de que todas se produzcan en un entorno similar. Tampoco es tan difícil crear una copia de seguridad antes de ejecutar una sustitución, en caso de que accidentalmente destruya algo que pierde profundidad de información.
MySQL 8.0+ puede usar REGEXP_REPLACE forma nativa.
12.5.2 Expresiones regulares :
REGEXP_REPLACE (expr, pat, repl [, pos [, ocurrence [, match_type]]])
Reemplaza las apariciones 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 expresiones regulares :
Anteriormente, MySQL usaba 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 proporciona soporte completo de Unicode y es seguro para multibytes. La función REGEXP_LIKE () realiza una comparación 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 encontrar posiciones de coincidencia y realizar la sustitución y extracción de subcadenas, respectivamente.
SELECT REGEXP_REPLACE('''',''[A-Zf]'',''-'',1,0,''c'');
-- Output:
-tackover-low
No.
Pero si tiene acceso a su servidor, podría usar una función definida por el usuario (UDF) como mysql-udf-regexp .
EDITAR: MySQL 8.0+ puede usar REGEXP_REPLACE de forma nativa. Más en respuesta arriba
Podemos usar la condición IF en la consulta SELECCIONAR como se muestra a continuación:
Supongamos que para cualquier cosa con "ABC", "ABC1", "ABC2", "ABC3", ..., queremos reemplazar con "ABC" y luego usar las condiciones 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'');
Recientemente escribí una función MySQL para reemplazar cadenas usando expresiones regulares. Puedes 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 ;
Ejecución de ejemplo:
mysql> select regex_replace(''[^a-zA-Z0-9/-]'','''',''2my test3_text-to. check // my- sql (regular) ,expressions ._,'');
Resolvemos este problema sin usar expresiones regulares. Esta consulta reemplaza solo la 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 abc
2 abc ajay
3 abc
Utilice MariaDB en su lugar. Tiene una funcion
REGEXP_REPLACE(col, regexp, replace)
Ver los documentos de MariaDB y las mejoras de expresiones regulares de PCRE.
Tenga en cuenta que también puede utilizar la agrupación de expresiones regulares (lo encontré muy útil):
SELECT REGEXP_REPLACE("", "(stack)(over)(flow)", ''//2 - //1 - //3'')
devoluciones
over - stack - flow
ACTUALIZACIÓN 2: Se ha proporcionado un conjunto útil de funciones de expresiones regulares que incluye REGEXP_REPLACE en MySQL 8.0. Esto hace que la lectura sea innecesaria a menos que esté limitado a usar una versión anterior.
ACTUALIZACIÓN 1: Ahora he hecho esto en una entrada de blog: http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html
Lo siguiente expande la función proporcionada por Rasika Godawatte, pero rastrea todas las subcadenas necesarias en lugar de solo probar caracteres individuales:
-- ------------------------------------------------------------------------------------
-- 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 ;
Manifestación
Limitaciones
- Este método, por supuesto, va a tomar un tiempo cuando la cadena de asunto es grande. Actualización: ahora se han agregado los parámetros de longitud de coincidencia mínima y máxima para mejorar la eficiencia cuando se conocen (cero = desconocido / ilimitado).
- No permitirá la sustitución de referencias inversas (por ejemplo,
/1,/2etc.) para reemplazar los grupos de captura. Si se necesita esta funcionalidad, vea esta respuesta que intenta proporcionar una solución mediante la actualización de la función para permitir una búsqueda y reemplazo secundario dentro de cada coincidencia encontrada (a costa de una mayor complejidad). - Si se usan
^y / o$en el patrón, deben estar al principio y al final, respectivamente, por ejemplo, no se admiten patrones como(^start|end$). - Hay una bandera "codiciosa" para especificar si el emparejamiento general debe ser codicioso o no codicioso. No se admite la combinación de coincidencias codiciosas y perezosas dentro de una sola expresión regular (por ejemplo,
a.*?b.*).
Ejemplos de uso
La función se ha utilizado para responder a las siguientes preguntas de :
- ¿Cómo contar palabras en MySQL / reemplazo de expresiones regulares?
- ¿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 numéricos no alfa de una cadena en MySQL?
- ¿Cómo reemplazar cada otra instancia de un personaje en particular en una cadena MySQL?