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.

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 | |
  • Thanks, this almost works... except the "for Carrier (and everything following that) is still getting returned – daniel9x Apr 17 '18 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 '18 at 17:07
  • Beautiful! Works like a charm. Thanks so much! – daniel9x Apr 17 '18 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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