How to Get Digits from String in MySQL

Sometimes you may need to extract number from string or numerical value from string in MySQL. Typically, we may have alphanumeric string and it can be tedious to extract numbers from it. In this article, we will learn how to get digits from string in MySQL.

How to Get Digits from String in MySQL

Let us say you have a column name in table data which contains numbers and characters, as shown below.

mysql> select name from data;
100683
101313
19924&
9072&h
12368&
5888&h
10308&
100664
1&hash
101104

and you want to extract numbers as shown below.

100683
101313
19924
9072
12368
5888 
10308
100664
1
101104

If you are using MySQL 8+ then you can do this using its inbuilt function REGEXP_SUBSTR(). Here is the query to get numbers from the above column.

$ select REGEXP_SUBSTR(name,"[0-9]+") as amount from `data`;

But please note, the above function returns only the initial occurrence of numbers. For example, if you have a field value ab123cd45 it will return only 123 and not 12345.

If you use MySQL <8.0 then there are couple of ways to do this. If your column values begin with number, then you can use cast() function to extract numbers from it. Here are a couple of examples to display it.

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

So you can use this function on name column above.

mysql> SELECT CAST(name AS UNSIGNED) from data;

If you want to extract numbers from arbitrary string, create a user defined function in MySQL first.

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 ;

Thereafter, you can use this function in your query. Here is an example to extract number from literal string.

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

Here is an example to extract number from name column in data table above.

mysql> SELECT ExtractNumber(name) AS number from data;

In this article, we have learnt several ways to extract number from string in MySQL. You can use any of them as per your requirement.

Also read:

How to Use SCP with PEM SSH Key
How to Ignore Certificate Errors in cURL
How to Get Creation Date for File & Folder
How to Delete Iptables Rules
How to Use Rsync with SSH Key