mysql 查第一个_如何使用MySQL查询在文本字段中找到第一个数字？

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?

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

Notes:

I'm using MySQL Server v8.0.

This pattern assumes that the_field is trimmed. Otherwise, use TRIM() first.