How Can I Extract Numbers from Alphanumeric Strings in MySQL?

How to extract numbers from MySQL string

When dealing with alphanumeric strings in MySQL, sometimes you need to extract the numbers in them. This can be achieved in a variety of ways.

Use explicit conversion

If the string starts with a numeric character, you can convert it to an integer using the CAST() function or adding 0 at the beginning. For example:

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

Extract numbers using a custom function

For more complex cases where the string may contain non-numeric characters, you can create a custom function to extract only the numbers. One such function is:

<code class="language-sql">CREATE FUNCTION `ExtractNumber`(in_string VARCHAR(50)) 
RETURNS INT
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</code>

Apply custom function

After defining a function, you can use it in a query to extract numbers from a string. For example:

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

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