How to Extract SubString between two positions

Question

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.

Answer 1

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.

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