Yesterday on EE I saw a very interesting request from a user for "Extracting numbers out of a string". This could be done in other languages with just 1 liner code but he needed it inside a SELECT query. http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24855357.html

So I came with a very small MySQL function which was doing as needed by the user. I'm not sure whther this is the best way to do this but "There is always room for improvement."

DELIMITER $$ 
 
 
 
DROP FUNCTION IF EXISTS `uExtractNumberFromString`$$
CREATE FUNCTION `uExtractNumberFromString`(in_string varchar(50)) RETURNS INT
NO SQL
 
 
BEGIN
 
 
DECLARE ctrNumber varchar(50);
DECLARE finNumber varchar(50) DEFAULT ' ';
DECLARE sChar varchar(2);
DECLARE inti INTEGER DEFAULT 1;
 
 
IF length(in_string) > 0 THEN
 
 
WHILE(inti <= length(in_string)) DO
    SET sChar= SUBSTRING(in_string,inti,1);
    SET ctrNumber= FIND_IN_SET(sChar,'0,1,2,3,4,5,6,7,8,9');
 
 
    IF ctrNumber > 0 THEN
 
 
       SET finNumber=CONCAT(finNumber,sChar);
    ELSE
 
 
       SET finNumber=CONCAT(finNumber,'');
    END IF;
 
 
    SET inti=inti+1;
END WHILE;
RETURN CAST(finNumber AS SIGNED INTEGER) ;
ELSE
  RETURN 0;
END IF;
 
 
END$$
 
 
 
SELECT uExtractNumberFromString('12;e1hhsak123s12'); 
12112312
 
 
 

You must be logged in to tag this tool

No Comments yet