Archivo de la etiqueta: mysql function

MySQL,Programación

Como extraer un valor numerico de una cadena en una consulta MySQL – MySQL Tips

21 comentarios

MySQLPuede ser que alguna vez nos veamos en la necesidad que extraer los números que hay almacenados dentro de campo en el cual están combinados los números y texto, por ejemplo ( 1 litro, 1 hora, 25 euros, $4,  horas), etc…)

Si el numero esta al principio de la cadena

En estas situación la solución es muy sencilla, y es la que nos encontraremos la mayoría de las veces, simplemente utilizamos la función CONVERT().

CONVERT() es una función que nos convierte datos entre distintos tipos de caracteres. A esta función le pasaremos como parámetros la cadena o el campo en el cual este el dato que queramos extraer y SIGNED, el cual indicara que queremos que nos lo convierta a un entero, la función quedaría así:


SELECT CONVERT('25 euros', SIGNED)

Esta consulta devolverá 25, pero que pasaría si el numero no estuviera al principio de la cadena de texto,  pues que nos devolverá 0. Por lo tanto solo nos sirve la función CONVERT() cuando la cadena empieza con numero si no no sirve.

Si el numero esta en el segundo lugar

Esta situación es un poco mas complicada, y tendremos que utilizar REGEXP() e IF.  Hace unos días ya hice una entrada referente a RLIKE y REGEXP así que decir que nos permite realizar búsquedas y comparaciones con patrones, y el IF generalmente se usa a la hora de crear procedimientos o funciones, pero también se puede usar dentro de una consulta, basicamente el IF se usa de la siguiente manera:

IF (condicion, si verdadero, si falso)

Imaginemos que nos han introducido los importes de esta manera ‘€ 25’, y queremos con una consulta sacar la suma de todos los importes.


SELECT SUM(
IF (
CONVERT(campo, SIGNED) != 0,
campo,
IF (
campo REGEXP('[0-9]'),
CONVERT(SUBSTR(campo FROM 2) , SIGNED),
0
)
)
)
FROM tabla;

Básicamente lo que hago es primero comprobar si hay un numero en el principio, en caso que que lo detecte lo devuelve, si no lo detecta, o devuelve 0, comprobamos con REGEXP() a ver si hay un numero en la cadena, en el caso que lo encuentre hago un CONVERT() desde la posición 2 de la cadena por medio de un SUBSTR(), si no devuelvo 0.

El numero puede estar en cualquier parte de la cadena

Para esta situación ya tenemos que recurrir a crear una función, no es recomendable ponernos a anidar IF. Partiendo de la combinación de REGEXP(), CONVERT() y SUBSTR() he realizado la siguiente función la cual nos devolverá el primer numero que encuentre en la cadena, este donde este.


DROP FUNCTION IF EXISTS extractNumber;
DELIMITER //
CREATE FUNCTION extractNumber (cadena1 VARCHAR(255))
RETURNS INT(11)
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE posicion, resultado, longitud INT(11) DEFAULT 0;
DECLARE cadena2 VARCHAR(255);
SET longitud = LENGTH(cadena1);
SET resultado = CONVERT(cadena1, SIGNED);
IF resultado = 0 THEN
IF cadena1 REGEXP('[0-9]') THEN
SET posicion = 2;
checkString:WHILE posicion <= longitud DO
SET cadena2 = SUBSTR(cadena1 FROM posicion);
IF CONVERT(cadena2, SIGNED) != 0 THEN
SET resultado = CONVERT(cadena2, SIGNED);
LEAVE checkString;
END IF;
SET posicion = posicion + 1;
END WHILE;
END IF;
END IF;
RETURN resultado;
END //
DELIMITER ;

view raw

extractNumber.sql

hosted with ❤ by GitHub

Limitaciones de la función extractNumber()

La mayoría de las pruebas las he realizado con números enteros, en plan «Un camión con 500 kilos de patatas» o en el plan «tortilla de 12 huevos», en todos esos casos funciona perfectamente, al igual que si ponemos «1€» o «€1» o «Total Euros: 20», pero si hay decimales solo devuelve la parte entera de estos.

Anuncio publicitario