My (SQL) WorkLog: MySQL - Extract numbers out of a string

Friday, October 30, 2009

MySQL - Extract numbers out of a string

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

4 comments:

Anonymous said...

This was insanely useful! Thanks for posting this!

Anonymous said...

Thanks! Exactly what I was looking for. It works!

Anonymous said...

Thanks a lot. Please note that this only works for integers that are maximum 9 digits long. If your result is greater than that, you need to adjust the type cast. I was using it for phone numbers so I simply recasted it as varchars since that's what I wanted any way.

Anonymous said...

This is just what i needed! Great Job!

Post a Comment