Search code examples
sqlregexpostgresqlsql-like

How to extract a number from a column with words and numbers in postgresql

I am trying to write a query in Postgresql where I need to extract the order number from a details column but I only want to extract the order number if the entry in the column contains "Used as payment on order x", with x being the number I need to extract. I'm not sure how to do this since the order numbers contain varying numbers of digits. Here is what I have so far-

Example string- "Used as payment on order 1034267" Desired output- 1034267

Example string- "Used as payment on order 55263" Desired output- 55263

SELECT details
FROM transactions
WHERE details LIKE 'Used as payment on order%'

But this gets the whole column entry and not just the number. How can I grab just the number from an entry?

Solution

  • This can easily be one using regexp_replace() and removing everything that is not a digit from the input string:

    SELECT regexp_replace(details, '[^0-9]+', '', 'g')
FROM transactions
WHERE details LIKE 'Used as payment on order%'