HL7 SQL

This feature allows you to use SQL language to aggregate data. One example is SELECT * WHERE *** CONTAINS 'SACRAMENTO' OR *** CONTAINS 'SAN JOSE,' which returned the following:

HL7 Path Syntax

An HL7 Path is defined as:

SEG[SEG_NUM]-F[REPEAT_NUM][[.C].S]

where:

SEG – a 3 character segment name. For example PID.

[SEG_NUM] – SEG_NUM is an integer value where SEG_NUM>0 or SEG_NUM='*'. If [SEG_NUM] is omitted, SEG[1] is assumed. If [SEG_NUM]='*', all matching segments are included in the search. For example, OBR[2] means the second OBR segment, whereas OBR[1] and OBR are equivalent.

F – is an integer value where F>0. For example, PID-3 means the 3^rd field in the PID segment.

[REPEAT_NUM]– is the Field Repeat number where REPEAT_NUM>0 or REPEAT_NUM='*'. If REPEAT_NUM='*' all repeating fields are included in the query. If [REPEAT_NUM] is omitted, F[1] is assumed.

C – C>0 or is absent. If C is absent, all components in the field are included separated by '^'. For example, PID-3 means PID-3.1 +PID-3.2+PID-3.3...PID-3.N.

S – S>0 or is absent. If S is absent, all subcomponents in the component are included separated by '&'. If S is omitted, S=1 is assumed. For example, PID-3.2.3 means the third subcomponent of the component of PID-3, and PID-3.2 means PID-3.2.1+PID-3.2.2+PID-3.2.3...PID-3.2.N.

Example HL7 Paths

For a stream of messages with a format similar to the following:

Query Syntax

SELECT [ TOP ( integer ) ] <HL7Path_list> [INTO TABLE_NAME] [where < search_condition >]

<HL7Path_list> ::= HL7Path [ [ AS ] column_alias ][,...n]

<search_condition > ::=

{ [ NOT ]  <search_condition>  }

[ { AND | OR } <search_condition>  ]

[ ,...n ]

<search_condition> ::=

{ HL7Path { = | < > | ! = | > | > = | < | < = } string_expression

| HL7Path [ NOT ] LIKE string_expression

| HL7Path [NOT] CONTAINS string_expression

| HL7Path [NOT] REGEX string_expression

| HL7Path IS [ NOT ] NULL

| HL7Path [ NOT ] IN ( string_list )

}

HL7Path can be any valid path, or *** which means the entire message

SQL Functions

Left(expression,match/count) - Returns the left most character from a string
parameter: expression - must evaluate to a string
parameter: match - characters to match,
or parameter count - number of characters
Example: Left('1234.56','.')='1234'
Example: Left('1234.56', 4)=='1234'

Right(expression,match/count) - Returns the right most characters from a string
parameter: expression - must evaluate to a string
parameter: match - characters to match
or parameter count - number of characters
Example: Right('1234.56','.')='56'
Example: Right('1234.56', 4)='34.56'

Trim(expression,string) - Trims characters from the start and end of the specified expression
parameter: expression - must evaluate to a string
parameter: string - characters to be trimmed
Example: Trim('001234.5600', 0)='1234.56'

LTrim(expression, string) - Trims characters from the left of the specified expression
parameter: expression - must evaluate to a string
parameter: string - characters to be trimmed
Example: Trim('001234.5600', 0)='1234.5600'

RTrim(expression, string) - Trims characters from the right of the specified expression
parameter: expression - must evaluate to a string
parameter: string - characters to be trimmed
Example: Trim('001234.5600', 0)='1234.5600'

ToUpper(expression) - Turns the expression to an uppercase value
parameter: expression - must evaluate to a string
Example: ToUppper('abc')='ABC'

ToLower(expression) - Turns the expression to an lowercase value
parameter: expression - must evaluate to a string
Example: ToLower('ABC')='abc'

Len(expression) - Returns the length of the specified expression
parameter: expression - must evaluate to a string
Example: Len('123456')=6

SubString(expression,offset,length) - Returns a portion of the given string using zero-based positioning
parameter: expression - must evaluate to a string
parameter: offset - offset into the string (first char is offset 1)
parameter: length - maximum characters to return
Example: SubString('001234.5600',2,4)='1234'

Query Examples

No where clause. Useful for exporting data

select MSH-7 as 'Date/Time', MSH-9, PID-3, PID-5, PV1-2

Select messages with matching text any where in the message.

select * where  *** CONTAINS 'SACRAMENTO' OR *** CONTAINS 'SAN JOSE'

Default path List, Find all male patients

select * where PID-8='M'

Find any illegal SSNs

select MSH-7, MSH-9, PID-3.2, PID-5, PID-19 as SSN

where PID-19!='' AND PID-19 NOT REGEX '^\d{3}-\d{2}-\d{4}$'

Find all Males and put them in a separate tab

select *, PID-8 into Males where PID-8='M'

Female birthdays < 1953 and male birthday less 1945

select MSH-7, MSH-9, PID-3, PID-5, PID-7, PID-8

where(PID-8='F' AND PID-7<='1953') OR (PID-8='M' AND PID-7<='1945');

Female patients that contain the last name 'SMITH'

select MSH-7 as 'Date/Time', MSH-9, PID-3, PID-5, PID-7

where PID-8='F' and PID-5.1.1 LIKE '%SMITH%';

Present, but Invalid SSN 'SMITH'

select MSH-7, MSH-9, PID-3, PID-5, PID-19 as SSN

where PID-19!='' AND PID-19 NOT REGEX '^\d{3}-\d{2}-\d{4}$';

Find Patient by original identifier, or by their new patient identifier

select MSH-7, MSH-9, PID-3, PID-5

where PID-3 IN ('800292182','800191207');