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 ?

52
demandé sur Steve Chambers 2011-08-04 18:12:59

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!

72
répondu Ryan Shillington 2018-04-24 13:57:03

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.

18
répondu Kevin Burton 2014-03-18 10:53:26
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

12
répondu Johan 2015-01-09 14:52:41

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 ;
6
répondu Alon Asulin 2016-12-21 06:33:37

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 .

5
répondu vdd 2014-03-20 10:32:07

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

4
répondu Abdel 2013-12-11 14:42:33

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.

4
répondu Артур Курицын 2018-04-30 10:39:07

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-привет ""]')
2
répondu userlond 2016-11-10 04:02:10

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';
1
répondu lfjeff 2016-04-15 20:46:51

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

1
répondu Kzqai 2016-09-20 19:17:51

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;
1
répondu Steve Chambers 2018-02-15 09:28:42

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;
0
répondu Alain Tiemblo 2015-03-31 13:49:44

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,' ',''),'-',''),'.',''),',',''),':',''),'?',''),'/',''),'!',''),'&',''),'@','')
0
répondu Josef Habr 2016-02-25 14:57:00

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>";
} 
0
répondu 2017-04-04 01:42:27

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.

0
répondu Luke Rehmann 2018-04-03 23:56:18

probablement une suggestion idiote comparée à d'autres:

if(!preg_match("/^[a-zA-Z0-9]$/",$string)){
    $sortedString=preg_replace("/^[a-zA-Z0-9]+$/","",$string);
}
-1
répondu sourRaspberri 2014-03-19 20:40:19

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)

-1
répondu ShadowElf 2017-04-05 15:18:28