SQL Query to compare (HL7) value at certain position in string based on delimiters (not position)
This works

SELECT *
FROM    
       TABLE
WHERE   
       $PIECE($PIECE(HL7Message,'|',4),'^',1) = 'This is the substring I want'


or to get Sendingfacilities based on other criteria

SELECT $PIECE($PIECE(HL7Message,'|',4),'^',1) as SendingFacility
FROM TABLE
WHERE &lt;whatever&gt;