How to find the first number in a text field using a MySQL query?

Question

I like to return only the first number of a text stored in a column of a database table. User have put in page ranges into a field like 'p.2-5' or 'page 2 to 5' or '2 - 5'. I am interested in the '2' here.

I tried to

  SELECT SUBSTR(the_field, LOCATE('2', the_field, 1)) AS 'the_number'
  FROM the_table

and it works. But how to get ANY number?

I tried

  SELECT SUBSTR(the_field, LOCATE(REGEXP '[0-9], the_field, 1)) AS 'the_number'
  FROM the_table

but this time I get an error.

Any ideas?

Answer 1

Just use REGEXP_SUBSTR():

SELECT REGEXP_SUBSTR(`the_field`,'^[0-9]+') AS `the_number` FROM `the_table`;

Notes:

1. I'm using MySQL Server v8.0.
2. This pattern assumes that the_field is trimmed. Otherwise, use TRIM() first.

Answer 2

For the specific case in the question. Where the String is {number}{string}{number} there is a simple solution to get the first number. In our case we had numbers like 1/2,3 4-10 1,2 and we were looking for the first number in each row.

It turned out that for this case one can use convert function to convert it into number. MySQL will return the first number

select convert(the_field ,SIGNED) as the_first_number from the_table

or more hard core will be

SELECT
the_field,
@num := CONVERT(the_field, SIGNED) AS cast_num,
SUBSTRING(the_field, 1, LOCATE(@num, the_field) + LENGTH(@num) - 1) AS num_part,
SUBSTRING(the_field, LOCATE(@num, the_field) + LENGTH(@num)) AS txt_part
FROM the_table;

This was original post at source by Eamon Daly

What does it do?

1. @num := CONVERT(the_field, SIGNED) AS cast_num # try to convert it into a number
2. SUBSTRING(the_field, 1, LOCATE(@num, the_field) + LENGTH(@num) - 1) # gets the number by using the length and the location of @num in field
3. SUBSTRING(the_field, LOCATE(@num, the_field) + LENGTH(@num)) # finds the rest of the string after the number.

Some thoughts for future use

Its worth keeping another column which will hold the first number after you parsed it before insert it to the database. Actually this is what we are doing these days.

Edit

Just saw that you have text like p.2-5 and etc.. which means the above cannot work as if the string does not start with a number convert return zero

Answer 3

There's no built-in way that I know of, but here's a Mysql function you can define, this will do it (I didn't code for minus-signs or non-integers, but those could of course be added).

Once created, you can use it like any other function:

SELECT firstNumber(the_field) from the_table;

Here's the code:

DELIMITER //
CREATE FUNCTION firstNumber(s TEXT)
    RETURNS INTEGER
    COMMENT 'Returns the first integer found in a string'
    DETERMINISTIC
    BEGIN

    DECLARE token TEXT DEFAULT '';
    DECLARE len INTEGER DEFAULT 0;
    DECLARE ind INTEGER DEFAULT 0;
    DECLARE thisChar CHAR(1) DEFAULT ' ';

    SET len = CHAR_LENGTH(s);
    SET ind = 1;
    WHILE ind <= len DO
        SET thisChar = SUBSTRING(s, ind, 1);
        IF (ORD(thisChar) >= 48 AND ORD(thisChar) <= 57) THEN
            SET token = CONCAT(token, thisChar);
        ELSEIF token <> '' THEN
            SET ind = len + 1;
        END IF;
        SET ind = ind + 1;
    END WHILE;

    IF token = '' THEN
        RETURN 0;
    END IF;

    RETURN token;

    END //
DELIMITER ;

Answer 4

REGEXP is not a function in MySQL, but something of an operator. Returns 1 if field matches the regular expression, or 0 if it does not. You cannot use it to figure out a position in a string.

Usage:

mysql> SELECT 'Monty!' REGEXP '.*';
    -> 1

As for answer to the question: I don't think there is a simple way to do that using MySQL only. You would be better off processing that field in the code, or extract values before inserting.