Programación en PHP, Javascript, Java, Python
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.

21 comentarios en “Como extraer un valor numerico de una cadena en una consulta MySQL – MySQL Tips”

  1. Pingback: TIP: Como extraer un valor numerico de una cadena en una consulta MySQL « DbRunas – Noticias y Recursos sobre Bases de Datos

  2. Muy interesante tu función Ruben, de verdad que admiro tu gran capacidad, una duda, si requiero aplicar esta función en PHP, como seria la traducción? cual seria la sintaxis correcta?.. Muchas gracias de antemano, Saludos desde México!.

    Responder

  3. hola amigo que buen post gracias era lo q necesitaba pero te pregunto como puedo usar la funcion dentro de una consulta.

    Responder

    1. Para poder acceder al valor desde la PHP una vez realizada la consulta puedes asignarle un alias al resultado, por ejemplo ‘SELECT a, b, convert(c) AS c from tabla’ a la hora de acceder accederíamos a a con $result[‘a’], y el convert seria $result[‘c’].

      Responder

  4. Luego de probar puede que les ayude en algo esta funcion
    DELIMITER $$

    DROP FUNCTION IF EXISTS `eschimu`.`extractNumber` $$
    CREATE DEFINER=`root`@`localhost` FUNCTION `extractNumber`(
    cadena1 VARCHAR(250)
    ) RETURNS VARCHAR(250)
    BEGIN
    DECLARE longitud INT(11) DEFAULT 0;

    SET longitud = LENGTH(TRIM(cadena1));
    SET @dCadena = »;
    SET @lcadena = 1;

    WHILE @lcadena <= longitud DO
    IF SUBSTRING(cadena1, @lcadena, 1) REGEXP('[0-9]') THEN
    SET @dCadena = CONCAT(@dCadena, SUBSTRING(cadena1, @lcadena, 1));
    END IF;
    SET @lcadena = @lcadena + 1;
    END WHILE;
    RETURN TRIM(@dCadena);

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

    Responder

  7. Buenas,
    Tengo un problema, mi cadena almacenada en la base de datos en una url, concretamente son como esta:
    index.php?var=17&otravar=12&masvar=33&muchovar=21

    Y me devuleve siempre el valor de la pirmera variable, en este caso 17.
    Cuando lo que yo quiero es que me devuelva el valor de masvar, o sea en este caso 33.

    ¿Puedes indicarme como hacerlo?
    Muchas gracias!

    Responder

      1. Gracias,
        Aunque la pase a encode, ¿que tengo que cambiar en la función para que busque masvar=XXXX
        Tu función actualmente busca la primer aparición de un número, pero en mi caso ese no sirve.
        Gracias de nuevo

      2. Entendi mal lo que me preguntabas, para poder realizar lo que necesitas, tienes que hacer una combinacion de parse_str y parse_url de la siguiente manera.

        $url = 'index.php?var=17&otravar=12&masvar=33&muchovar=21';
$params = array();
parse_str(parse_url($url, PHP_URL_QUERY), $params);
/**
* $params ahora contien contiene:
array(4) {
  'var' =>
  string(2) "17"
  'otravar' =>
  string(2) "12"
  'masvar' =>
  string(2) "33"
  'muchovar' =>
  string(2) "21"
}
*/

  8. Bueno, perdona, que busque «masvar=XXXX» donde XXXX sea numerico y que me devuelva ese valor, por que en la actualidad aunque ponga en el regexp masvar= me sigue devolviendo el numero que sigue a var=

    Esta es de esas veces en las que una tontería me hace perder mucho tiempo, como odio estas situaciones…
    Muchas gracias de nuevo.

    Responder

  12. Muchas gracias Ruben, excelente aporte, tuve la necesidad de extraer letras tambien y lo acomode de la siguiente manera, este caso sirve para extraer numeros y letras, solo basta con cambiar la expresion regular y queda listo.

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `bd_supermercado`.`PRUEBA_`$$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `PRUEBA_`
    (
    pr_cadena VARCHAR(255)

    )
    BEGIN

    DECLARE var_i,var_longitud INT DEFAULT 0;
    DECLARE var_caracter CHAR;
    DECLARE var_resultado VARCHAR(255) DEFAULT »;

    SET var_longitud = LENGTH(pr_cadena);

    WHILE var_i < var_longitud DO

    SET var_i = var_i+1;

    SET var_caracter = SUBSTRING(pr_cadena,var_i,1);

    IF var_caracter REGEXP('[a-z]') THEN
    SET var_resultado = CONCAT(var_resultado,var_caracter);
    END IF;

    END WHILE;

    SELECT var_i,var_resultado;

    END$$

    DELIMITER ;

    Responder

  13. HOLA TENGO UN PROBLEMA tengo que convertir esto y no tengo idea de como realizarlo me podrías ayudar
    Escribe una consulta para convertir las calificaciones numéricas a alfabéticas en base a las siguientes expresiones.
    Calificación = 10 -> A
    Calificación >= 9 -> B
    Calificación >= 8 -> C
    Calificación >= 7 -> D
    Calificación >= 6 -> D
    Calificación F

    Responder

Deja un comentario