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 :)

1 Answer 1


This works

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.