Search code examples
mysqlstringintegerrow

Get integer followed by string

I would like to make a MySQL database view pertaining to the 'quantity' of a submission, which will be formatted like this: 

Anderson | Wood | $897.55 : 548.78$, Quantity: 3**
....

There will seemingly always be a 'Quantity: " string followed by the quantity number. Is there a way to find the integer that comes after another particular string? This way I can put the quantity in its own row and use that to dynamically find the price.

Solution

  • If you have a string in that form, you can use substring_index() and trim():

    select trim(substring_index(submission, 'Quantity:', -1)) + 0

    This splits the string at 'Quantity:', removes the leading spaces and converts the value to an integer.