How to Extract SubString between two positions
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.