CODEWITHSUNDEEP
mysqlsubstringsubstr
Dave
Dave

Reputation: 19090

In MySQL, how can I extract a string after a specific string pattern?

I'm using MySQL 5.5.37. One of my VARCHAR columns returns string data of the form

Description Number 9 MOre description

Every string will have the word "Number" followed by an actual number, whether it be one or multiple digits.

My quesiton is how do I select only the portion of the string that follows the word "Number," taht is the number? In the example above, my select statement would return "9".

Upvotes: 1

Views: 1708

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Try using substring_index to get the number if there is numeric value after "Number" else null:

select 
    case 
        when col regexp '.*Number [0-9]+.*' 
        then substring_index(substring_index(col, 'Number ', -1), ' ', 1)
    end 
from t;

Demo

Upvotes: 1

Related Questions