SQL Query – Compare HL7 Value at Certain Position in String

substring

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|^~\&|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 '<some regex>' but I get the following error:

[Error Code: 14, SQL State: 37000] [SQLCODE: <-14>:<A comparison operator is required here>]

If this looks like HL7 that's because it is 🙂

Best Answer

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 <whatever>