MySQL tira caracteres no numéricos para comparar

Estoy buscando encontrar registros en una tabla que coincida con un número específico que el usuario ingrese. Entonces, el usuario puede ingresar 12345, pero podría ser 123zz4-5 en la base de datos.

Imagino que algo así funcionaría si las funciones de PHP funcionaran en MySQL.

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

¿Cuál es la función equivalente o la forma de hacerlo con solo MySQL?

Me doy cuenta de que este es un tema antiguo pero al buscar en Google este problema no pude encontrar una solución simple (vi a los venerables agentes pero creo que esta es una solución más simple) así que aquí hay una función que escribí, parece funcionar bastante bien.

 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 $$ 

Si bien no es bonito y muestra resultados que no coinciden, esto ayuda a:

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

Todavía me gustaría encontrar una mejor solución similar al elemento en la pregunta original.

No hay reemplazo regexp, solo una cadena simple REPLACE ().

MySQL tiene el operador REGEXP, pero es solo un probador de partidos y no un sustituto, por lo que tendrías que volver la lógica al revés:

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

Esto es como su versión con LIKE pero coincide con más precisión. Ambos funcionarán igual de mal, y necesitan un escaneo completo de tabla sin índices.

La respuesta más votada (@ user1467716) no es la más rápida. ¡Felicitaciones a todos por dar una propuesta de trabajo para rebotar!

Esta es una versión mejorada:

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

Prueba 5000 veces en un servidor de prueba:

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

La forma más simple en que puedo pensar es utilizar el operador MySQL REGEXP a la:

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

No es especialmente bonito, pero MySQL no tiene una función preg_replace , así que creo que es lo mejor que obtendrás.

Personalmente, si estos datos numéricos son tan importantes, mantendría un campo separado solo para contener los datos eliminados. Hará tus búsquedas mucho más rápidas que con la búsqueda de expresiones regulares.

Esta publicación de blog detalla cómo quitar los caracteres no numéricos de una cadena a través de una función de MySQL:

SELECT NumericOnly("asdf11asf");

regresa 11

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

Puede hacer fácilmente lo que quiera con REGEXP_REPLACE (compatible con MySQL 8+ y MariaDB 10.0.5+)

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

Reemplaza las ocurrencias en la cadena expr que coinciden con la expresión regular especificada por el patrón pat con la cadena de reemplazo repl, y devuelve la cadena resultante. Si expr, pat o repl es NULL, el valor de retorno es NULL.

Ir a REGEXP_REPLACE doc: MySQL o MariaDB

Intentalo:

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

Salida:

 12312333 

Tengo una situación similar, uniendo productos a códigos de barras donde el código de barras no almacena números alfanuméricos a veces, así que 102.2234 en el DB debe encontrarse al buscar 1022234.

Al final acabo de agregar un nuevo campo, reference_number a las tablas de productos, y tengo php strip los none alpha numerics en el product_number para rellenar reference_number cada vez que se agregan nuevos productos.

Tendrá que hacer un análisis de una sola vez de la tabla para crear todos los campos reference_number para los productos existentes.

A continuación, puede configurar su índice, incluso si la velocidad no es un factor para esta operación, sigue siendo una buena idea mantener la base de datos ejecutándose bien para que esta consulta no lo atasque y ralentice otras consultas.

Me encontré con esta solución. La respuesta principal de user1467716 funcionará en phpMyAdmin con un pequeño cambio: agregue una segunda etiqueta delimitadora al final del código.

La versión de phpMyAdmin es 4.1.14; MySQL versión 5.6.20

También agregué un limitador de longitud usando

DECLARE count INT DEFAULT 0; en las declaraciones

AND count < 5 en la instrucción WHILE

SET COUNT=COUNT+1; en la statement IF

Forma definitiva:

 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 

No hay reemplazo de expresiones regulares en lo que a mí respecta, pero encontré esta solución;

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

Funciona para mí y uso MySQL 5.0

También encontré este lugar que podría ayudar.

¿Qué tan grande es la mesa con foo? Si es pequeño, y la velocidad realmente no importa, puede extraer el ID de la fila y el foo, recorrerlo usando las funciones de reemplazo de PHP para comparar, y luego extraer la información que desea por número de fila.

Por supuesto, si la mesa es demasiado grande, esto no funcionará bien.

prueba este ejemplo esto se usa para números de teléfono, sin embargo, puede modificarlo para sus necesidades.

  -- 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 esta función definida por el usuario (UDF). digamos que tienes una columna de números de teléfono:

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

 select remove_alpha(col1) from mytable 

El resultado sería;

 5139833983 18383389898 9838898383