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:

Open

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 TAB_NAME [APPEND]] [where < search_condition >]

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

INTO TAB_NAME [APPEND] - Directs the query results into a new tab, with the option to APPEND the results to the first tab that shares the same name as TAB_NAME. When the APPEND option is selected, duplicates are automatically removed, allowing you to run multiple queries and merge their results into a single tab without duplication in the result set.

SQL-Style Function Reference

String Functions

LEFT(expression, matchOrCount)

Returns the left portion of a string.

• expression: string

• matchOrCount: either

  • string → everything before the first occurrence of this string (case-sensitive, ordinal), or

  • int → number of characters to take from the left (≥1)

Examples

• Left('1234.56', '.') → '1234'

• Left('1234.56', 4) → '1234'

RIGHT(expression, matchOrCount)

Returns the right portion of a string.

• expression: string

• matchOrCount: either

  • string → everything after the first occurrence of this string (case-sensitive, ordinal), or

  • int → number of characters to take from the right (≥1; clamped to string length)

Examples

• Right('1234.56', '.') → '56'

• Right('1234.56', 4) → '4.56'

TRIM(expression, chars)

Trims all leading and trailing occurrences of the specified characters.

• expression: string

• chars: characters to trim (string; each char is trimmed)

Example
Trim('001234.5600', '0') → '1234.56'

LTRIM(expression, chars)

Trims all leading occurrences of the specified characters.

• expression: string

• chars: characters to trim

Example
LTrim('001234.5600', '0') → '1234.5600'

RTRIM(expression, chars)

Trims all trailing occurrences of the specified characters.

• expression: string

• chars: characters to trim

Example
RTrim('001234.5600', '0') → '1234.56'

TOUPPER(expression)

Uppercases a string.

• expression: string

Example
ToUpper('abc') → 'ABC'

TOLOWER(expression)

Lowercases a string.

• expression: string

Example
ToLower('ABC') → 'abc'

LENGTH(expression)

Returns the character length of a string.

• expression: string

Example
Length('123456') → 6

INDEXOF(expression, value)

Returns the zero-based index of the first occurrence of value in expression, or -1 if not found. Case-sensitive (Ordinal).

• expression: string

• value: string

Example
IndexOf('ABCDEF', 'CD') → 2

SPLIT(expression, matchChars, index)

Splits a string by any character in matchChars and returns the element at index.
If the expression is empty or index is out of range, returns ''.

• expression: string

• matchChars: string (treated as a set of delimiter characters)

• index: int (≥0)

Example
Split('A|B,C', '|,', 1) → 'B'

SUBSTRING(expression, index, length)

Returns a substring using zero-based indexing. Indices/lengths are clamped to valid ranges; negative index/length returns the original string.

• expression: string

• index: int (0-based)

• length: int (≥0)

Example
SubString('001234.5600', 2, 4) → '1234'

REPLACE(expression, oldValue, newValue)

Replaces all occurrences of oldValue with newValue.

• expression: string

• oldValue: string

• newValue: string

Example
Replace('a-b-a', 'a', 'x') → 'x-b-x'

REGEXREPLACE(expression, pattern, replacement)

Performs a .NET regular expression replace.

• expression: string

• pattern: regex pattern (string)

• replacement: string

Example
RegexReplace('A123B', '\d+', '#') → 'A#B'

REMOVE(expression, s1, s2, ..., sN)

Removes all occurrences of each specified string from expression.

• expression: string

• s1..sN: strings to remove (≥1)

Example
Remove('1-23-45', '-', '3') → '1245'

COALESCE(e1, e2, ..., eN)

Returns the first non-empty string among the evaluated expressions.
If an argument is an HL7PathExpression, it’s skipped when it resolves to null via HL7PathResolver.

• e1..eN: 1 or more expressions

Example
Coalesce('', '', 'X', 'Y') → 'X'

FILTERSTRING(expression, allowedCharacters)

Keeps only characters that appear in allowedCharacters.

• expression: string

• allowedCharacters: string (set of allowed chars)

Example
FilterString('(123)-456', '0123456789') → '123456'

ESCAPE(expression)

Escapes expression using the current HL7 message’s encoding characters.

• expression: string

UNESCAPE(expression)

Unescapes expression using the current HL7 message’s encoding characters.

• expression: string

Math & Numeric Functions

All numeric conversions use .NET conversions as in the implementation.

• ABS(x) → absolute value
  Abs(-3.2) → 3.2

• ACOS(x), ASIN(x), ATAN(x) → trigonometric inverses (radians)

• CEILING(x) → smallest integer ≥ x

• COS(x), SIN(x), TAN(x) → trigonometric functions (radians)

• EXP(x) → e^x

• FLOOR(x) → largest integer ≤ x

• IEEEREMAINDER(x, y) → IEEE 754 remainder

• LOG(x, base) → logarithm with specified base

• LOG10(x) → base-10 logarithm

• POW(x, y) → x^y

• ROUND(x, digits) → rounds to digits fractional digits

• SIGN(x) → −1, 0, or 1

• MAX(a, b), MIN(a, b) → numeric max/min (decimal conversion)

Examples

• Round(3.14159, 2) → 3.14

• Pow(2, 10) → 1024

• Log(8, 2) → 3

Conditional

IF(condition, thenExpr, elseExpr)

Evaluates and returns thenExpr when condition is true; otherwise elseExpr.

• condition: boolean

• thenExpr/elseExpr: any

Example
If(Length('X') = 1, 'yes', 'no') → 'yes'

Date/Time

GETDATE([format])

Returns the current date/time formatted with a .NET format string.
Default format: 'yyyyMMddHHmmss'.

• format: optional string

Examples

• GetDate() → e.g., '20250816093012'

• GetDate('yyyyMMddHHmmss.ffff zzzz')

Notes & Behavior Details

• All string searches (LEFT/RIGHT with string, INDEXOF, SPLIT) use case-sensitive, ordinal comparisons (no culture/regex).

• SUBSTRING and INDEXOF are zero-based.

• Many functions validate argument counts/types and will throw descriptive ArgumentExceptions on mismatch.

Query Examples

No where clause. Useful for exporting data

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

Select male patients into a tab named Males

select * INTO Males where PID-8='M'

Select male patients appended to an existing (or new) tab named Males

select * INTO Males APPEND where PID-8='M'

Select messages with matching text any where in the message.

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

Select messages where any OBX segment contains 'FOO BAR'.

select * where  OBX[*] CONTAINS 'FOO BAR'

Select messages where any OBX-5, or OBX-5 repeat, contains 'FOO BAR'.

select * where  OBX[*]-5[*] CONTAINS 'FOO BAR'

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%';