How Can I Extract Digits from Strings in MySQL?

Extract numbers from string in MySQL

When dealing with alphanumeric strings, numbers need to be separated for various data processing needs. MySQL provides several ways to achieve this goal.

For strings starting with a numeric character followed by a non-numeric character, you can use CAST() or implicit conversion:

<code class="language-sql">SELECT CAST('1234abc' AS UNSIGNED); -- 1234
SELECT '1234abc'+0; -- 1234</code>

To extract numbers from an arbitrarily structured string, you can define a custom function like ExtractNumber:

<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>

After defining the custom function ExtractNumber, you can use it in queries:

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

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