intermedios - ¿Cómo eliminar todos los caracteres no alfanuméricos de una cadena en MySQL?
mysql quitar espacios intermedios (17)
Estoy trabajando en una rutina que compara cadenas, pero para una mejor eficiencia, necesito eliminar todos los caracteres que no sean letras o números.
Estoy usando varias funciones REPLACE ahora, pero ¿tal vez hay una solución más rápida y más agradable?
Basado en share , modificado para trabajar con cadenas de más de 255 caracteres y preservar espacios de la cadena original.
FYI hay lower(str) al final.
Lo usé para comparar cadenas:
DROP FUNCTION IF EXISTS spacealphanum;
DELIMITER $$
CREATE FUNCTION `spacealphanum`( str TEXT ) RETURNS TEXT CHARSET utf8
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret TEXT DEFAULT '''';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c REGEXP ''[[:alnum:]]'' THEN
SET ret=CONCAT(ret,c);
ELSEIF c = '' '' THEN
SET ret=CONCAT(ret," ");
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
SET ret = lower(ret);
RETURN ret;
END $$
DELIMITER ;
Desde el punto de vista del rendimiento (y asumiendo que lees más de lo que escribes)
Creo que la mejor manera sería precalcular y almacenar una versión eliminada de la columna. De esta forma, harás la transformación menos.
Luego puede poner un índice en la nueva columna y hacer que la base de datos haga el trabajo por usted.
Esto se puede hacer con una función de reemplazo de expresión regular que publiqué en otra respuesta y he publicado un blog here . Puede que no sea la solución más eficiente posible y podría parecer demasiado para el trabajo en cuestión, pero al igual que una navaja suiza, puede ser útil por otras razones.
Se puede ver en acción eliminando todos los caracteres no alfanuméricos en esta demostración en línea de Rextester .
SQL (excluyendo el código de función por brevedad) :
SELECT txt,
reg_replace(txt,
''[^a-zA-Z0-9]+'',
'''',
TRUE,
0,
0
) AS `reg_replaced`
FROM test;
Hasta ahora, el único enfoque alternativo menos complicado que las otras respuestas aquí es determinar el conjunto completo de caracteres especiales de la columna, es decir, todos los caracteres especiales que están en uso en esa columna en este momento, y luego hacer un reemplazo secuencial de todos esos personajes, por ejemplo
update pages set slug = lower(replace(replace(replace(replace(name, '' '', ''''), ''-'', ''''), ''.'', ''''), ''&'', '''')); # replacing just space, -, ., & only
.
Esto solo es recomendable en un conjunto de datos conocido, de lo contrario es trivial que algunos personajes especiales pasen desapercibidos con un enfoque de lista negra en lugar de un enfoque de lista blanca.
Obviamente, la forma más simple es validar previamente los datos fuera de sql debido a la falta de una lista blanca integrada sólida (por ejemplo, mediante una sustitución de expresiones regulares).
He escrito este UDF. Sin embargo, solo recorta caracteres especiales al comienzo de la cadena. También convierte la cadena en minúsculas. Puede actualizar esta función si lo desea.
DELIMITER //
DROP FUNCTION IF EXISTS DELETE_DOUBLE_SPACES//
CREATE FUNCTION DELETE_DOUBLE_SPACES ( title VARCHAR(250) )
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN
DECLARE result VARCHAR(250);
SET result = REPLACE( title, '' '', '' '' );
WHILE (result <> title) DO
SET title = result;
SET result = REPLACE( title, '' '', '' '' );
END WHILE;
RETURN result;
END//
DROP FUNCTION IF EXISTS LFILTER//
CREATE FUNCTION LFILTER ( title VARCHAR(250) )
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN
WHILE (1=1) DO
IF( ASCII(title) BETWEEN ASCII(''a'') AND ASCII(''z'')
OR ASCII(title) BETWEEN ASCII(''A'') AND ASCII(''Z'')
OR ASCII(title) BETWEEN ASCII(''0'') AND ASCII(''9'')
) THEN
SET title = LOWER( title );
SET title = REPLACE(
REPLACE(
REPLACE(
title,
CHAR(10), '' ''
),
CHAR(13), '' ''
) ,
CHAR(9), '' ''
);
SET title = DELETE_DOUBLE_SPACES( title );
RETURN title;
ELSE
SET title = SUBSTRING( title, 2 );
END IF;
END WHILE;
END//
DELIMITER ;
SELECT LFILTER('' !@#$%^&*()_+1a b'');
Además, puede usar expresiones regulares, pero esto requiere instalar una extensión MySql.
Intenté algunas soluciones pero al final utilicé replace . Mi conjunto de datos son números de parte y sé exactamente qué esperar. Pero solo por cordura, utilicé PHP para construir la consulta larga:
$dirty = array('' '', ''-'', ''.'', '','', '':'', ''?'', ''/'', ''!'', ''&'', ''@'');
$query = ''part_no'';
foreach ($dirty as $dirt) {
$query = "replace($query,''$dirt'','''')";
}
echo $query;
Esto produce algo que solía tener un dolor de cabeza de:
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(part_no,'' '',''''),''-'',''''),''.'',''''),'','',''''),'':'',''''),''?'',''''),''/'',''''),''!'',''''),''&'',''''),''@'','''')
La forma más rápida que pude encontrar (y usar) es con convert().
del Doc. CONVERT () con USING se usa para convertir datos entre diferentes conjuntos de caracteres.
Ejemplo:
convert(string USING ascii)
En su caso, el conjunto de caracteres correcto se definirá a sí mismo
NOTA del Doc. La forma de USO de CONVERT() está disponible desde 4.1.0 .
Necesario para reemplazar caracteres no alfanuméricos en lugar de eliminar caracteres no alfanuméricos, por lo que he creado esto en base al alfanum de Ryan Shillington. Funciona para cadenas de hasta 255 caracteres de longitud
DROP FUNCTION IF EXISTS alphanumreplace;
DELIMITER |
CREATE FUNCTION alphanumreplace( str CHAR(255), d CHAR(32) ) RETURNS CHAR(255)
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(32) DEFAULT '''';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c REGEXP ''[[:alnum:]]'' THEN SET ret=CONCAT(ret,c);
ELSE SET ret=CONCAT(ret,d);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END |
DELIMITER ;
Ejemplo:
select ''hello world!'',alphanum(''hello world!''),alphanumreplace(''hello world!'',''-'');
+--------------+--------------------------+-------------------------------------+
| hello world! | alphanum(''hello world!'') | alphanumreplace(''hello world!'',''-'') |
+--------------+--------------------------+-------------------------------------+
| hello world! | helloworld | hello-world- |
+--------------+--------------------------+-------------------------------------+
Necesitarás agregar la función del alfanum por separado si quieres eso, solo lo tengo aquí para el ejemplo.
Necesitaba obtener solo caracteres alfabéticos de una cadena en un procedimiento, e hice:
SET @source = "whatever you want";
SET @target = '''';
SET @i = 1;
SET @len = LENGTH(@source);
WHILE @i <= @len DO
SET @char = SUBSTRING(@source, @i, 1);
IF ((ORD(@char) >= 65 && ORD(@char) <= 90) || (ORD(@char) >= 97 && ORD(@char) <= 122)) THEN
SET @target = CONCAT(@target, @char);
END IF;
SET @i = @i + 1;
END WHILE;
Ninguna de estas respuestas funcionó para mí. Tuve que crear mi propia función llamada alfanum, que me quitó los caracteres:
DROP FUNCTION IF EXISTS alphanum;
DELIMITER |
CREATE FUNCTION alphanum( str CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(255) DEFAULT '''';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c REGEXP ''[[:alnum:]]'' THEN
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END |
DELIMITER ;
Ahora puedo hacer:
select ''This works finally!'', alphanum(''This works finally!'');
y obtengo:
+---------------------+---------------------------------+
| This works finally! | alphanum(''This works finally!'') |
+---------------------+---------------------------------+
| This works finally! | Thisworksfinally |
+---------------------+---------------------------------+
1 row in set (0.00 sec)
¡Viva!
Probablemente una sugerencia tonta en comparación con otros:
if(!preg_match("/^[a-zA-Z0-9]$/",$string)){
$sortedString=preg_replace("/^[a-zA-Z0-9]+$/","",$string);
}
Solución directa y contrastada para caracteres latinos y cirílicos:
DELIMITER //
CREATE FUNCTION `remove_non_numeric_and_letters`(input TEXT)
RETURNS TEXT
BEGIN
DECLARE output TEXT DEFAULT '''';
DECLARE iterator INT DEFAULT 1;
WHILE iterator < (LENGTH(input) + 1) DO
IF SUBSTRING(input, iterator, 1) IN
(''0'', ''1'', ''2'', ''3'', ''4'', ''5'', ''6'', ''7'', ''8'', ''9'', ''A'', ''B'', ''C'', ''D'', ''E'', ''F'', ''G'', ''H'', ''I'', ''J'', ''K'', ''L'', ''M'', ''N'', ''O'', ''P'', ''Q'', ''R'', ''S'', ''T'', ''U'', ''V'', ''W'', ''X'', ''Y'', ''Z'', ''a'', ''b'', ''c'', ''d'', ''e'', ''f'', ''g'', ''h'', ''i'', ''j'', ''k'', ''l'', ''m'', ''n'', ''o'', ''p'', ''q'', ''r'', ''s'', ''t'', ''u'', ''v'', ''w'', ''x'', ''y'', ''z'', ''А'', ''Б'', ''В'', ''Г'', ''Д'', ''Е'', ''Ж'', ''З'', ''И'', ''Й'', ''К'', ''Л'', ''М'', ''Н'', ''О'', ''П'', ''Р'', ''С'', ''Т'', ''У'', ''Ф'', ''Х'', ''Ц'', ''Ч'', ''Ш'', ''Щ'', ''Ъ'', ''Ы'', ''Ь'', ''Э'', ''Ю'', ''Я'', ''а'', ''б'', ''в'', ''г'', ''д'', ''е'', ''ж'', ''з'', ''и'', ''й'', ''к'', ''л'', ''м'', ''н'', ''о'', ''п'', ''р'', ''с'', ''т'', ''у'', ''ф'', ''х'', ''ц'', ''ч'', ''ш'', ''щ'', ''ъ'', ''ы'', ''ь'', ''э'', ''ю'', ''я'')
THEN
SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
END IF;
SET iterator = iterator + 1;
END WHILE;
RETURN output;
END //
DELIMITER ;
Uso:
-- outputs "hello12356"
SELECT remove_non_numeric_and_letters(''hello - 12356-привет ""]'')
Tenga cuidado, los caracteres como ''o'' son considerados como alfa por MySQL. Es mejor usar algo como:
SI c ENTRE ''a'' Y ''z'' O c ENTRE ''A'' Y ''Z'' O c ENTRE ''0'' Y ''9'' O c = ''-'' ENTONCES
Tuve un problema similar al tratar de hacer coincidir los apellidos en nuestra base de datos que eran ligeramente diferentes. Por ejemplo, a veces las personas ingresan el nombre de la misma persona como "McDonald" y también como "Mc Donald", o "San Juan" y "San Juan".
En lugar de tratar de convertir los datos Mysql, resolví el problema creando una función (en PHP) que tomaría una cadena y crearía una expresión regular solo alfa:
function alpha_only_regex($str) {
$alpha_only = str_split(preg_replace(''/[^A-Z]/i'', '''', $str));
return ''^[^a-zA-Z]*''.implode(''[^a-zA-Z]*'', $alpha_only).''[^a-zA-Z]*$'';
}
Ahora puedo buscar en la base de datos con una consulta como esta:
$lastname_regex = alpha_only_regex($lastname);
$query = "SELECT * FROM my_table WHERE lastname REGEXP ''$lastname_regex'';
la función del alfanum (respuesta automática) tiene un error, pero no sé por qué. Para el texto "cas synt ls 75W140 1L", devuelve "cassyntls75W1401", "L" del final falta algo de cómo.
Ahora uso
delimiter //
DROP FUNCTION IF EXISTS alphanum //
CREATE FUNCTION alphanum(prm_strInput varchar(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE v_char VARCHAR(1);
DECLARE v_parseStr VARCHAR(255) DEFAULT '' '';
WHILE (i <= LENGTH(prm_strInput) ) DO
SET v_char = SUBSTR(prm_strInput,i,1);
IF v_char REGEXP ''^[A-Za-z0-9]+$'' THEN
SET v_parseStr = CONCAT(v_parseStr,v_char);
END IF;
SET i = i + 1;
END WHILE;
RETURN trim(v_parseStr);
END
//
(encontrado en google)
si estás usando php entonces ...
try{
$con = new PDO ("mysql:host=localhost;dbname=dbasename","root","");
}
catch(PDOException $e){
echo "error".$e-getMessage();
}
$select = $con->prepare("SELECT * FROM table");
$select->setFetchMode(PDO::FETCH_ASSOC);
$select->execute();
while($data=$select->fetch()){
$id = $data[''id''];
$column = $data[''column''];
$column = preg_replace("/[^a-zA-Z0-9]+/", " ", $column); //remove all special characters
$update = $con->prepare("UPDATE table SET column=:column WHERE id=''$id''");
$update->bindParam('':column'', $column );
$update->execute();
// echo $column."<br>";
}
SELECT teststring REGEXP ''[[:alnum:]]+'';
SELECT * FROM testtable WHERE test REGEXP ''[[:alnum:]]+'';
Ver: http://dev.mysql.com/doc/refman/5.1/en/regexp.html
Desplácese hacia abajo a la sección que dice: [:character_class:]
Si quieres manipular cadenas, la forma más rápida será usar str_udf, mira:
https://github.com/hholzgra/mysql-udf-regexp