How to Extract Digits from a String in MySQL?

Use CAST function and custom function to extract numbers from strings in MySQL

MySQL’s CAST function can extract numbers from a string, but the prerequisite is that the string must start with a number. To extract numbers from any string, you can use a custom function, such as the following code:

<code class="language-sql">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);
            IF sChar BETWEEN '0' AND '9' 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 ;</code>

Using this function, numbers can be extracted from strings in MySQL queries:

<code class="language-sql">SELECT ExtractNumber("abc1234def") AS number;</code>

This query will return 1234 because the custom function iterates through the string, identifies the numeric characters, and concatenates them into a single number.

The above is the detailed content of How to Extract Digits from a String in MySQL?. For more information, please follow other related articles on the PHP Chinese website!