SQL Query to compare (HL7) value at certain position in string based on delimiters (not position) This is complicated by the fact that I don't know what DBMS the backend uses.  I know it's Cache, but I don't seem to be able to do anything but the most basic commands.  I cannot seem to SET or DECLARE variables.

Here is an example:


  MSH|^~\&amp;|unimportant|This is the substring I want^123123123^ABC|unimportant|...


So let's call that specific substring SendingFacility.  What is the simplest, most compatible way, to write something like this:

SELECT * WHERE SendingFacility = 'This is the substring I want'


I tried SELECT WHERE SendingFacility REGEXP '&lt;some regex&gt;' but I get the following error:

[Error Code: 14, SQL State: 37000]  [SQLCODE: &lt;-14&gt;:&lt;A comparison operator is required here&gt;]

If this looks like HL7 that's because it is :)
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;