How Can I Extract Numbers from Strings in MySQL?

MySQL string number extraction method

MySQL provides multiple methods to extract numbers from strings. If the string starts with a number, you can use the CAST() function or an implicit type conversion (e.g., '1234abc' 0) to extract the numeric part.

Custom function to process any string

For strings that contain non-numeric characters or numbers not at the beginning of the string, you can create a custom function, such as ExtractNumber:

<code class="language-sql">CREATE FUNCTION `ExtractNumber`(in_string VARCHAR(50))
RETURNS INT
NO SQL
BEGIN
    DECLARE inti INT DEFAULT 0;
    DECLARE sChar CHAR(1);
    DECLARE finNumber VARCHAR(50) DEFAULT "";
    DECLARE strLength INT;

    SET strLength = LENGTH(in_string);

    WHILE(inti < strLength) DO
        SET sChar = SUBSTRING(in_string, inti + 1, 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);
END</code>

Usage of custom function ExtractNumber

After defining the ExtractNumber function, it can be used in queries:

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

Numbers can be extracted from MySQL strings in various formats by using this function or the CAST() method.

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