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

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.