How to get only Digits from String in mysql?

Question

I have some string output which contain alphanumeric value. I want to get only Digits from that string. how can I fetch this by query? which MySql function can I Use?

My query is like :

select DISTINCT SUBSTRING(referrerURL,71,6)    from   hotshotsdblog1.annonymoustracking    where  advertiserid = 10  limit  10; 

Output :

100683 101313 19924& 9072&h 12368& 5888&h 10308& 100664 1&hash 101104 

And I Want output like :

100683 101313 19924 9072 12368 5888  10308 100664 1 101104 

Answer 1

If the string starts with a number, then contains non-numeric characters, you can use the CAST() function or convert it to a numeric implicitly by adding a 0:

SELECT CAST('1234abc' AS UNSIGNED); -- 1234 SELECT '1234abc'+0; -- 1234 

To extract numbers out of an arbitrary string you could add a custom function like this:

DELIMITER $$  CREATE FUNCTION `ExtractNumber`(in_string VARCHAR(50))  RETURNS INT NO SQL BEGIN     DECLARE ctrNumber VARCHAR(50);     DECLARE finNumber VARCHAR(50) DEFAULT '';     DECLARE sChar VARCHAR(1);     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);             END IF;             SET inti = inti + 1;         END WHILE;         RETURN CAST(finNumber AS UNSIGNED);     ELSE         RETURN 0;     END IF;     END$$  DELIMITER ; 

Once the function is defined, you can use it in your query:

SELECT ExtractNumber("abc1234def") AS number; -- 1234