Search code examples
mysqlsubstringsubstr

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".

Solution

  • 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