Skip to content

Instantly share code, notes, and snippets.

@sbarrat

sbarrat/extractNumber.sql Secret

Last active Dec 21, 2015
Embed
What would you like to do?
Function to extract a number from a string in MySQL
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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.