How to Get only number from string mysql

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.

Answer

You can use the REGEXP_SUBSTR function for this.

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