How to Get only number from string mysql-CodePudding

I have a column called Equipment in my table that contains something like this: store(2)

I need to create a new column and select only the number from the text otherwise we put 1.

Example:

SELECT ID, Name, 
CASE WHEN 'equipment contains number then put this number' ELSE 'we put 1' END AS Quantity FROM my_table.

Thanks.

CodePudding user response：

You can use the REGEXP_SUBSTR function for this.

SELECT IF(REGEXP_SUBSTR('store(2)','[0-9] ') != '', REGEXP_SUBSTR('store(2)','[0-9] '), 1);