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.