Caracteres não numéricos da tira do MySQL para comparar

Eu estou olhando para encontrar registros em uma tabela que correspondem a um número específico que o usuário entra. Assim, o usuário pode inserir 12345, mas isso pode ser 123zz4-5 no database.

Eu imagino que algo assim funcionaria, se as funções PHP funcionassem no MySQL.

SELECT * FROM foo WHERE preg_replace("/[^0-9]/","",bar) = '12345' 

Qual é a function ou maneira equivalente de fazer isso apenas com o MySQL?

Eu percebo que este é um tópico antigo, mas ao pesquisar este problema eu não consegui encontrar uma solução simples (eu vi os agentes veneráveis, mas acho que esta é uma solução mais simples) então aqui está uma function que eu escrevi, parece funcionar muito bem.

 DROP FUNCTION IF EXISTS STRIP_NON_DIGIT; DELIMITER $$ CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255)) RETURNS VARCHAR(255) BEGIN DECLARE output VARCHAR(255) 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' ) THEN SET output = CONCAT(output, SUBSTRING(input, iterator, 1)); END IF; SET iterator = iterator + 1; END WHILE; RETURN output; END $$ 

Embora não seja bonito e mostre resultados que não correspondem, isso ajuda:

 SELECT * FROM foo WHERE bar LIKE = '%1%2%3%4%5%' 

Eu ainda gostaria de encontrar uma solução melhor semelhante ao item na pergunta original.

Não há nenhuma substituição de expressão regular, apenas uma string simples REPLACE ().

O MySQL tem o operador REGEXP, mas é apenas um testador de correspondência, não um substituto, então você teria que transformar a lógica de dentro para fora:

 SELECT * FROM foo WHERE bar REGEXP '[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*'; 

É como a sua versão com o LIKE, mas corresponde com mais precisão. Ambos terão um desempenho igualmente ruim, precisando de uma varredura completa da tabela sem índices.

A resposta mais votada (@ user1467716) não é a mais rápida. Parabéns a eles por darem uma proposta de trabalho para se destacar!

Esta é uma versão melhorada:

 DELIMITER ;; DROP FUNCTION IF EXISTS `STRIP_NON_DIGIT`;; CREATE DEFINER=`root`@`localhost` FUNCTION `STRIP_NON_DIGIT`(input VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8 READS SQL DATA BEGIN DECLARE output VARCHAR(255) DEFAULT ''; DECLARE iterator INT DEFAULT 1; DECLARE lastDigit INT DEFAULT 1; DECLARE len INT; SET len = LENGTH(input) + 1; WHILE iterator < len DO -- skip past all digits SET lastDigit = iterator; WHILE ORD(SUBSTRING(input, iterator, 1)) BETWEEN 48 AND 57 AND iterator < len DO SET iterator = iterator + 1; END WHILE; IF iterator != lastDigit THEN SET output = CONCAT(output, SUBSTRING(input, lastDigit, iterator - lastDigit)); END IF; WHILE ORD(SUBSTRING(input, iterator, 1)) NOT BETWEEN 48 AND 57 AND iterator < len DO SET iterator = iterator + 1; END WHILE; END WHILE; RETURN output; END;; 

Testando 5000 vezes em um servidor de teste:

 -- original Execution Time : 7.389 sec Execution Time : 7.257 sec Execution Time : 7.506 sec -- ORD between not string IN Execution Time : 4.031 sec -- With less substrings Execution Time : 3.243 sec Execution Time : 3.415 sec Execution Time : 2.848 sec 

A maneira mais simples que posso pensar é usar o operador MySQL REGEXP a la:

 WHERE foo LIKE '1\D*2\D*3\D*4\D*5' 

Não é especialmente bonito, mas o MySQL não tem uma function preg_replace , então acho que é o melhor que você vai conseguir.

Pessoalmente, se esses dados numéricos fossem tão importantes, eu manteria um campo separado apenas para conter os dados excluídos. Isso tornará suas pesquisas muito mais rápidas do que com a pesquisa de expressões regulares.

Esta postagem do blog detalha como remover caracteres não numéricos de uma string por meio de uma function MySQL:

SELECT NumericOnly("asdf11asf");

devolve 11

http://venerableagents.wordpress.com/2011/01/29/mysql-numeric-functions/

Você pode facilmente fazer o que quiser com o REGEXP_REPLACE (compatível com o MySQL 8+ e o MariaDB 10.0.5+)

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

Substitui as ocorrências na string expr que correspondem à expressão regular especificada pelo padrão pat com a substituição da string de substituição e retorna a string resultante. Se expr, pat ou repl for NULL, o valor de retorno será NULL.

Vá para REGEXP_REPLACE doc: MySQL ou MariaDB

Tente:

 SELECT REGEXP_REPLACE('123asd12333', '[a-zA-Z]+', ''); 

Saída:

 12312333 

Eu tenho uma situação semelhante, combinando produtos para códigos de barras onde o código de barras não armazena nenhum alfa numérico às vezes, então 102.2234 no database precisa ser encontrado ao procurar por 1022234.

No final, acabei de adicionar um novo campo, reference_number às tabelas de produtos, e remover do php os nenhum alfa numérico no product_number para preencher reference_number sempre que novos produtos forem adicionados.

Você precisaria fazer uma varredura única da tabela para criar todos os campos reference_number para produtos existentes.

Você pode então configurar seu índice, mesmo que a velocidade não seja um fator para essa operação, ainda é uma boa idéia manter o database funcionando bem, então essa consulta não atrapalha e diminui a velocidade de outras consultas.

Eu me deparei com essa solução. A resposta top por user1467716 funcionará no phpMyAdmin com uma pequena alteração: adicione uma segunda tag delimitadora ao final do código.

A versão do phpMyAdmin é 4.1.14; Versão do MySQL 5.6.20

Eu também adicionei um limitador de comprimento usando

DECLARE count INT DEFAULT 0; nas declarações

AND count < 5 na instrução WHILE

SET COUNT=COUNT+1; na declaração IF

Forma final:

 DROP FUNCTION IF EXISTS STRIP_NON_DIGIT; DELIMITER $$ CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255)) RETURNS VARCHAR(255) BEGIN DECLARE output VARCHAR(255) DEFAULT ''; DECLARE iterator INT DEFAULT 1; DECLARE count INT DEFAULT 0; WHILE iterator < (LENGTH(input) + 1) AND count < 5 DO --limits to 5 chars IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN SET output = CONCAT(output, SUBSTRING(input, iterator, 1)); SET COUNT=COUNT+1; END IF; SET iterator = iterator + 1; END WHILE; RETURN output; END $$ DELIMITER $$ --added this 

Não há regex replace, tanto quanto eu estou preocupado, mas eu encontrei esta solução;

 --Create a table with numbers DROP TABLE IF EXISTS ints; CREATE TABLE ints (i INT UNSIGNED NOT NULL PRIMARY KEY); INSERT INTO ints (i) VALUES ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20); --Then extract the numbers from the specified column SELECT bar, GROUP_CONCAT(SUBSTRING(bar, i, 1) ORDER BY i SEPARATOR '') FROM foo JOIN ints ON i BETWEEN 1 AND LENGTH(bar) WHERE SUBSTRING(bar, i, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') GROUP BY bar; 

Ele funciona para mim e eu uso o MySQL 5.0

Também encontrei este lugar que poderia ajudar.

Quão grande é a mesa com foo? Se for pequeno, e a velocidade realmente não importa, você pode puxar o ID de linha e foo, fazer um loop sobre ele usando as funções de substituição do PHP para comparar, e então puxar a informação que você quer pelo número da linha.

É claro que, se a mesa for muito grande, isso não funcionará bem.

tente este exemplo. isso é usado para números de telefone, mas você pode modificá-lo para as suas necessidades.

  -- function removes non numberic characters from input -- returne only the numbers in the string CREATE DEFINER =`root`@`localhost` FUNCTION `remove_alpha`(inputPhoneNumber VARCHAR(50)) RETURNS VARCHAR(50) CHARSET latin1 DETERMINISTIC BEGIN DECLARE inputLenght INT DEFAULT 0; -- var for our iteration DECLARE counter INT DEFAULT 1; -- if null is passed, we still return an tempty string DECLARE sanitizedText VARCHAR(50) DEFAULT ''; -- holder of each character during the iteration DECLARE oneChar VARCHAR(1) DEFAULT ''; -- we'll process only if it is not null. IF NOT ISNULL(inputPhoneNumber) THEN SET inputLenght = LENGTH(inputPhoneNumber); WHILE counter < = inputLenght DO SET oneChar = SUBSTRING(inputPhoneNumber, counter, 1); IF (oneChar REGEXP ('^[0-9]+$')) THEN SET sanitizedText = Concat(sanitizedText, oneChar); END IF; SET counter = counter + 1; END WHILE; END IF; RETURN sanitizedText; END 

para usar essa function definida pelo usuário (UDF). digamos que você tenha uma coluna de números de telefone:

 col1 (513)983-3983 1-838-338-9898 phone983-889-8383 

 select remove_alpha(col1) from mytable 

O resultado seria;

 5139833983 18383389898 9838898383