2

I have a text column (emailbody) in a table (notification) which keeps a record of the HTML that went out for a particular email. Somewhere inside that HTML file, there is a line of text that contains the following line:

Delayed ${shipmentId} for Carrier

Where ${shipmentId} represents the specific Id that was generated for that particular record. Example: 12345

Basically I want to query the column in the table for all the shipmentId values, which unfortunately will involve getting the string positions between Delayed and for Carrier. I understand this will likely involve using one of Postgres' regular expression functions but I'm not quite sure how to do it, or if there's something a little simpler.

Improve this question

1 Answer 1

Reset to default
2

The SUBSTRING function has regex support, so the following would work:

SELECT SUBSTRING(emailbody FROM 'Delayed (.*?) for Carrier') FROM notification

The so-called capturing group () is to make sure you capture only the shipment ID and not the entire string "Delayed 12345 for Carrier". The ? makes the capture non-greedy, to make sure the first 'for Carrier' is matched.

Improve this answer
3
  • Thanks, this almost works... except the "for Carrier (and everything following that) is still getting returned
    – daniel9x
    Apr 17, 2018 at 17:02
  • Ah, a classic greedy regex. That's probably because you have another 'for Carrier' in the email body. Please try my updated version.
    – Glorfindel
    Apr 17, 2018 at 17:07
  • Beautiful! Works like a charm. Thanks so much!
    – daniel9x
    Apr 17, 2018 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.