Comment supprimer tous les caractères non alpha d’une chaîne dans MySQL?

Aucune de ces réponses n’a fonctionné pour moi. J’ai dû créer ma propre fonction appelée alphanum qui m’a dépouillé des caractères:

 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 faire:

 select 'This works finally!', alphanum('This works finally!'); 

et je reçois:

 +---------------------+---------------------------------+ | This works finally! | alphanum('This works finally!') | +---------------------+---------------------------------+ | This works finally! | Thisworksfinally | +---------------------+---------------------------------+ 1 row in set (0.00 sec) 

Hourra!

Du sharepoint vue de la performance (et en supposant que vous lisiez plus que vous écrivez)

Je pense que le meilleur moyen serait de pré-calculer et stocker une version dépouillée de la colonne, de cette façon, vous faites moins la transformation.

Vous pouvez ensuite mettre un index sur la nouvelle colonne et obtenir la firebase database pour faire le travail pour vous.

 SELECT testssortingng REGEXP '[[:alnum:]]+'; SELECT * FROM testtable WHERE test REGEXP '[[:alnum:]]+'; 

Voir: http://dev.mysql.com/doc/refman/5.1/en/regexp.html
Faites défiler jusqu’à la section qui dit: [:character_class:]

Si vous voulez manipuler des chaînes, le moyen le 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 des chaînes de plus de 255 caractères et en préservant les espaces de la chaîne d’origine.

FYI il y a lower(str) à la fin.

Je l’ai utilisé pour comparer des chaînes:

 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 ().

de Doc. CONVERT () avec USING est utilisé pour convertir des données entre différents jeux de caractères.

Exemple:

 convert(ssortingng USING ascii) 

Dans votre cas, le bon jeu de caractères sera défini automatiquement

NOTE de Doc. La forme USING de CONVERT() est disponible à partir de 4.1.0 .

Attention, les caractères comme ‘ou’ sont considérés comme 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 = ‘-‘ alors

J’ai écrit ce fichier UDF. Cependant, il ne taille que les caractères spéciaux au début de la chaîne. Il convertit également la chaîne en minuscule. 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'); 

En outre, vous pouvez utiliser des expressions régulières, mais cela nécessite l’installation d’une extension MySql.

Solution droite et battletested pour les caractères latins et 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 ; 

Usage:

 -- 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 firebase database qui étaient légèrement différents. Par exemple, les personnes ont parfois saisi le nom de la même personne que “McDonald” et aussi “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 et créerait une expression régulière alpha uniquement:

 function alpha_only_regex($str) { $alpha_only = str_split(preg_replace('/[^AZ]/i', '', $str)); return '^[^a-zA-Z]*'.implode('[^a-zA-Z]*', $alpha_only).'[^a-zA-Z]*$'; } 

Maintenant, je peux rechercher dans la firebase database avec une requête comme celle-ci:

 $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 consiste à déterminer l’ensemble complet des caractères spéciaux de la colonne, c’est-à-dire tous les caractères spéciaux utilisés dans cette colonne, puis à remplacer séquentiellement tous ces caractères, par exemple

 update pages set slug = lower(replace(replace(replace(replace(name, ' ', ''), '-', ''), '.', ''), '&', '')); # replacing just space, -, ., & only 

.

Ceci n’est conseillé que sur un dataset connu, sinon il est sortingvial que certains caractères spéciaux passent outre une approche de liste noire au lieu d’une approche de liste blanche.

De toute évidence, le moyen le plus simple est de pré-valider les données en dehors de sql en raison du manque de listes blanches intégrées robustes (par exemple, via un remplacement de regex).

Cela peut être fait avec une fonction de remplacement d’expression régulière que j’ai postée dans une autre réponse et que j’ai bloguée ici . Ce n’est peut-être pas la solution la plus efficace possible et peut sembler exagérée pour le travail en cours – mais comme un couteau suisse, il peut être utile pour d’autres raisons.

On peut le voir en action en supprimant tous les caractères non alphanumériques de cette démonstration en ligne Rextester .

SQL (excluant le code de fonction pour la brièveté) :

 SELECT txt, reg_replace(txt, '[^a-zA-Z0-9]+', '', TRUE, 0, 0 ) AS `reg_replaced` FROM test; 

Je devais obtenir uniquement des caractères alphabétiques d’une chaîne dans une procédure et 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 . Mon dataset est composé de numéros de pièces et je sais très bien à quoi s’attendre. Mais juste pour la santé mentale, 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; 

Cela produit quelque chose que j’avais l’habitude d’obtenir avec un mal de tête de:

 replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(part_no,' ',''),'-',''),'.',''),',',''),':',''),'?',''),'/',''),'!',''),'&',''),'@','') 

si vous utilisez php alors ….

 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."
"; } 

Nécessaire pour remplacer les caractères non-alphanumériques plutôt que de supprimer les caractères non alphanumériques, donc j’ai créé cela basé sur alphanum de Ryan Shillington. Fonctionne pour les chaînes de 255 caractères maximum

 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 devez append la fonction alphanum séparément si vous le souhaitez, je l’ai juste ici pour l’exemple.

Probablement une suggestion stupide par rapport aux autres:

 if(!preg_match("/^[a-zA-Z0-9]$/",$ssortingng)){ $sortedSsortingng=preg_replace("/^[a-zA-Z0-9]+$/","",$ssortingng); } 

la fonction alphanum (auto-répondu) a un bug, mais je ne sais pas pourquoi. Pour le texte “cas synt ls 75W140 1L” retour “cassyntls75W1401”, “L” de la fin en manque certains 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)