Comment supprimer tous les caractères numériques non-alpha d'une chaîne de caractères dans MySQL?
je travaille sur une routine qui compare les chaînes, mais pour une meilleure efficacité je dois supprimer tous les caractères qui ne sont pas des lettres ou des nombres.
j'utilise plusieurs fonctions REPLACE maintenant, mais peut-être qu'il y a une solution plus rapide et plus agréable ?
17 réponses
aucune de ces réponses n'a fonctionné pour moi. J'ai dû créer ma propre fonction appelée alphanum qui a dépouillé les chars pour moi:
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 ;
Maintenant je peux le faire:
select 'This works finally!', alphanum('This works finally!');
et j'obtiens:
+---------------------+---------------------------------+
| This works finally! | alphanum('This works finally!') |
+---------------------+---------------------------------+
| This works finally! | Thisworksfinally |
+---------------------+---------------------------------+
1 row in set (0.00 sec)
Hourra!
du point de vue des performances, (et en supposant que vous lisez plus que vous n'écrivez)
je pense que la meilleure façon serait de pré-calculer et stocker une version dépouillée de la colonne, De cette façon, vous faites la transformation moins.
Vous pouvez ensuite mettre un index sur la colonne et obtenir la base de données pour faire le travail pour vous.
SELECT teststring REGEXP '[[:alnum:]]+';
SELECT * FROM testtable WHERE test REGEXP '[[:alnum:]]+';
voir: http://dev.mysql.com/doc/refman/5.1/en/regexp.html
Faites défiler vers le bas jusqu'à la section qui dit: [:character_class:]
si vous voulez manipuler les chaînes de caractères la manière la plus rapide sera d'utiliser un str_udf, voir:
https://github.com/hholzgra/mysql-udf-regexp
Basé sur la réponse de Ryan Shillington , modifié pour fonctionner avec les chaînes de plus de 255 caractères et de préserver les espaces de la chaîne d'origine.
POUR INFO il y a lower(str) à la fin.
j'ai utilisé ceci pour comparer les cordes:
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 ;
le moyen le plus rapide que j'ai pu trouver (et utiliser ) est avec convert().
du Doc. CONVERT () avec USING est utilisé pour convertir des données entre différents jeux de caractères.
exemple:
convert(string USING ascii)
dans votre cas, le droit jeu de caractères sera défini par vous-même
NOTE du Doc. Le formulaire utilisant le formulaire de CONVERT() est disponible à partir de 4.1.0 .
attention, les caractères comme " ou " sont considérés comme des alpha par MySQL. Il vaut mieux utiliser quelque chose comme :
si c Entre "a" et " z "ou c entre" A " et " Z "ou c entre" 0 " et '9' OU c = '-' PUIS
j'ai écrit cet UDF. Cependant, il ne garnitures de caractères spéciaux au début de la chaîne. Il convertit également la chaîne en minuscules. Vous pouvez mettre à jour cette fonction si vous le souhaitez.
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');
aussi, vous pouvez utiliser des expressions régulières mais cela nécessite l'installation d'une extension MySql.
Droit et battletested solution pour l'amérique latine et les caractères cyrilliques:
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 ;
Utilisation:
-- outputs "hello12356"
SELECT remove_non_numeric_and_letters('hello - 12356-привет ""]')
j'ai eu un problème similaire en essayant de faire correspondre les noms de famille dans notre base de données qui étaient légèrement différents. Par exemple, il arrive que des personnes entrent le nom de la même personne comme "McDonald" et aussi comme "Mc Donald", ou "St John" et "St John".
au lieu d'essayer de convertir les données Mysql, j'ai résolu le problème en créant une fonction (en PHP) qui prendrait une chaîne de caractères et créerait une expression régulière alpha seulement:
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]*$';
}
maintenant je peux recherche dans la base de données avec une requête comme ceci:
$lastname_regex = alpha_only_regex($lastname);
$query = "SELECT * FROM my_table WHERE lastname REGEXP '$lastname_regex';
jusqu'à présent, la seule approche alternative moins compliquée que les autres réponses ici est de déterminer l'ensemble complet des caractères spéciaux de la colonne, c'est-à-dire tous les caractères spéciaux qui sont utilisés dans cette colonne en ce moment, et puis faire un remplacement séquentiel de tous ces caractères, par exemple
update pages set slug = lower(replace(replace(replace(replace(name, ' ', ''), '-', ''), '.', ''), '&', '')); # replacing just space, -, ., & only
.
C'est seulement conseillé sur un ensemble de données, sinon c'est trivial pour certains personnages spéciaux pour passer avec un approche de la liste noire au lieu d'une approche de la liste blanche.
évidemment, la manière la plus simple est de pré-valider les données en dehors de sql en raison de l'absence de liste blanche intégrée robuste (par exemple via un remplacement regex).
cela peut être fait avec une fonction de remplacement d'expression régulière que j'ai posté dans une autre réponse et ont blogué sur ici . Ce n'est peut - être pas la solution la plus efficace possible et cela pourrait sembler exagéré pour le travail en cours-mais comme un couteau de l'armée suisse, cela peut être utile pour d'autres raisons.
, Il peut être vu dans l'action en supprimant tous les caractères non-alphanumériques ce Rextester démo en ligne .
SQL (à l'exclusion du code de fonction pour la brièveté)) :
SELECT txt,
reg_replace(txt,
'[^a-zA-Z0-9]+',
'',
TRUE,
0,
0
) AS `reg_replaced`
FROM test;
j'avais besoin de n'obtenir que des caractères alphabétiques d'une chaîne dans une procédure, et j'ai fait:
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;
j'ai essayé quelques solutions, mais à la fin utilisé replace . Mes données sont des numéros de pièce et je sais assez à quoi m'attendre. Mais juste pour la raison, J'ai utilisé PHP pour construire la longue requête:
$dirty = array(' ', '-', '.', ',', ':', '?', '/', '!', '&', '@');
$query = 'part_no';
foreach ($dirty as $dirt) {
$query = "replace($query,'$dirt','')";
}
echo $query;
cette sortie quelque chose que j'ai utilisé pour obtenir un mal de tête de:
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(part_no,' ',''),'-',''),'.',''),',',''),':',''),'?',''),'/',''),'!',''),'&',''),'@','')
si vous utilisez php ensuite....
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>";
}
nécessaire à remplacer les caractères non alphanumériques plutôt que supprimer les caractères non alphanumériques donc j'ai créé ceci basé sur L'alphanumme de Ryan Shillington. Œuvres pour cordes jusqu'à 255 caractères
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 ;
exemple:
select 'hello world!',alphanum('hello world!'),alphanumreplace('hello world!','-');
+--------------+--------------------------+-------------------------------------+
| hello world! | alphanum('hello world!') | alphanumreplace('hello world!','-') |
+--------------+--------------------------+-------------------------------------+
| hello world! | helloworld | hello-world- |
+--------------+--------------------------+-------------------------------------+
Vous aurez besoin d'ajouter la fonction numérique séparément si vous voulez, je viens de l'avoir ici, pour l'exemple.
probablement une suggestion idiote comparée à d'autres:
if(!preg_match("/^[a-zA-Z0-9]$/",$string)){
$sortedString=preg_replace("/^[a-zA-Z0-9]+$/","",$string);
}
la fonction alphanum (SELF answered) a un bug, mais je ne sais pas pourquoi. Pour le texte "cas synt ls 75W140 1L "return " cassyntls75W1401", "L" de la fin manque un peu de comment.
maintenant j'utilise
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
//
(trouvé sur google)