Como remover todos os caracteres não alfanuméricos de uma string no MySQL?

Estou trabalhando em uma rotina que compara strings, mas para melhor eficiência eu preciso remover todos os caracteres que não são letras ou números.

Estou usando várias funções REPLACE agora, mas talvez haja uma solução mais rápida e mais agradável?

Nenhuma dessas respostas funcionou para mim. Eu tive que criar minha própria function chamada alphanum que despojou os caracteres para mim:

 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 ; 

Agora eu posso fazer:

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

e eu recebo:

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

Viva!

Do ponto de vista do desempenho, (e no pressuposto de que você lê mais do que escreve)

Eu acho que a melhor maneira seria calcular e armazenar uma versão despojada da coluna, dessa forma você faz a transformação menos.

Você pode então colocar um índice na nova coluna e obter o database para fazer o trabalho para você.

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

Veja: http://dev.mysql.com/doc/refman/5.1/en/regexp.html
Role para baixo até a seção que diz: [:character_class:]

Se você quiser manipular strings o caminho mais rápido será usar um str_udf, veja:
https://github.com/hholzgra/mysql-udf-regexp

Baseado na resposta de Ryan Shillington , modificado para trabalhar com strings com mais de 255 caracteres e preservando espaços da string original.

FYI lá é lower(str) no final.

Eu usei isso para comparar strings:

 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 ; 

A maneira mais rápida que consegui encontrar (e usar) é com convert ().

do Doc. CONVERT () com USING é usado para converter dados entre diferentes conjuntos de caracteres.

Exemplo:

 convert(string USING ascii) 

No seu caso, o conjunto de caracteres certo será auto-definido

NOTA do Doc. O formulário USING do CONVERT() está disponível a partir do 4.1.0 .

Cuidado, caracteres como ‘ou’ são considerados alfa pelo MySQL. É melhor usar algo como:

SE c ENTRE ‘a’ E ‘z’ OU c ENTRE ‘A’ E ‘Z’ OU c ENTRE ‘0’ E ‘9’ OU c = ‘-‘ ENTÃO

Eu escrevi este UDF. No entanto, apenas apara caracteres especiais no início da cadeia. Também converte a string para minúscula. Você pode atualizar esta function, se desejar.

 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'); 

Além disso, você pode usar expressões regulares, mas isso requer a instalação de uma extensão MySQL.

Solução recta e battletested para caracteres latinos e 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-привет ""]') 

Eu tive um problema semelhante ao tentar combinar os sobrenomes em nosso database que eram um pouco diferentes. Por exemplo, às vezes as pessoas entravam no nome da mesma pessoa como “McDonald” e também como “Mc Donald”, ou “St John” e “St. John”.

Em vez de tentar converter os dados do Mysql, resolvi o problema criando uma function (em PHP) que pegaria uma string e criaria uma expressão regular somente alfa:

 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]*$'; } 

Agora eu posso pesquisar o database com uma consulta como esta:

 $lastname_regex = alpha_only_regex($lastname); $query = "SELECT * FROM my_table WHERE lastname REGEXP '$lastname_regex'; 

Até agora, a única abordagem alternativa menos complicada do que as outras respostas aqui é determinar o conjunto completo de caracteres especiais da coluna, ou seja, todos os caracteres especiais que estão em uso nessa coluna no momento e, em seguida, fazer uma substituição sequencial de todos esses personagens, por exemplo

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

.

Isso é aconselhável em um conjunto conhecido de dados, caso contrário, é trivial que alguns caracteres especiais sejam ignorados com uma abordagem de lista negra em vez de uma abordagem de lista de permissions.

Obviamente, a maneira mais simples é pré-validar os dados fora do sql devido à falta de listas de permissions integradas robustas (por exemplo, por meio de uma substituição regex).

Isso pode ser feito com uma function de substituição de expressão regular que postei em outra resposta e escrevi sobre o blog aqui . Pode não ser a solução mais eficiente possível e pode parecer excessiva para o trabalho em questão – mas, como um canivete suíço, pode ser útil por outras razões.

Pode ser visto em ação removendo todos os caracteres não alfanuméricos nesta demo on-line do Rextester .

SQL (excluindo o código de function para brevidade) :

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

Eu precisava obter apenas caracteres alfabéticos de uma string em um procedimento e fiz:

 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; 

Eu tentei algumas soluções, mas no final usado replace . Meu dataset é um número de peça e sei o que esperar. Mas apenas para sanidade, usei o PHP para construir a consulta longa:

 $dirty = array(' ', '-', '.', ',', ':', '?', '/', '!', '&', '@'); $query = 'part_no'; foreach ($dirty as $dirt) { $query = "replace($query,'$dirt','')"; } echo $query; 

Isso produz algo que eu usei para obter uma dor de cabeça de:

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

Se você estiver usando php então ….

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

Necessário para replace caracteres não alfanuméricos, em vez de remover caracteres não alfanuméricos, então criei isso com base no alphanum de Ryan Shillington. Funciona para seqüências de caracteres de até 255 caracteres de comprimento

 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 ; 

Exemplo:

 select 'hello world!',alphanum('hello world!'),alphanumreplace('hello world!','-'); +--------------+--------------------------+-------------------------------------+ | hello world! | alphanum('hello world!') | alphanumreplace('hello world!','-') | +--------------+--------------------------+-------------------------------------+ | hello world! | helloworld | hello-world- | +--------------+--------------------------+-------------------------------------+ 

Você precisará adicionar a function alphanum separadamente se quiser, eu só a tenho aqui para o exemplo.

Provavelmente uma sugestão boba em comparação com os outros:

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

a function alphanum (auto-respondida) tem um bug, mas não sei porque. Para o texto “cas synt ls 75W140 1L” retorno “cassyntls75W1401”, “L” do final está faltando algum como.

Agora eu 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 no google)