/
HL7 SQL

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 3rd 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:

MSH|^~\&|SPC|M||M|20040503223716||ORM^O01|176201653|P|2.2| PID|1||0000307656^^^M&FEE&&FIE&&FOO&&FUM^MR~0000858462^^^P&FOO&BAR^MR OBR|1||3844834|2035^NM HEPATOBILIARY DUCT^MRD|||200405030939|| OBX|1|ST|&GDT|1|TEXT1~TEXT2|| OBX|2|ST|&GDT|1|TEXT3~TEXT4|| OBX|3|ST|&GDT|1|TEXT5~TEXT6||

Messages where the first patient identifier is 0000307656

PID-3.1 = '0000307656'

Messages where the 2nd patient identifier is 0000307656

PID-3[2].1 = '0000307656'

Messages where any patient identifier is 00003076566

PID-3[*].1 = '0000307656'

Messages where any Namespace ID (PID-4.2) is FEE

PID-3[*].4.2 = 'FEE'

Messages where any OBX-5, or repeat there of, has the word TEXT

OBX[*]-5[*] CONTAINS 'TEXT'

Messages where the OBR-4 contains exactly 2035^NM HEPATOBILIARY DUCT^MRD

OBR-4 = '2035^NM HEPATOBILIARY DUCT^MRD'

Query Syntax

SELECT [ TOP ( integer ) ] <HL7Path_list> [INTO TAB_NAME [APPEND]] [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

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'

TABLE(inputExpression, key1 -> value1, key2 -> value2, ..., ? -> defaultValue)

Maps an input string to a result string using a fixed set of key/value pairs. The optional ? marker defines the default value returned when the input does not match any key. Without a default, the input itself is returned unchanged.

inputExpression: string (typically an HL7 path)

key -> value pairs: 1 or more 'literalKey' -> 'literalValue' mappings (both sides are string literals)

? -> defaultValue: optional default, returned when no key matches

Notes

Key lookup is case-insensitive.

If the same key appears more than once, the first mapping wins.

If ? is specified more than once, the last one wins.

The empty string can be used as a key (e.g. '' -> 'Unknown') to match empty/missing field values explicitly.

Examples

Table(PID-8, 'F'->'Female', 'M'->'Male', 'O'->'Other', ?->'Unknown')
→ 'Female' when PID-8='F', 'Male' when PID-8='M', 'Unknown' for anything else (including empty).

Table(PID-8, ''->'Empty', 'F'->'Female', ?->'Other')
→ 'Empty' when PID-8 is missing; otherwise the matched mapping or 'Other'.

CASE ... END

A SQL-style multi-branch conditional. Two forms are supported:

Searched form - each WHEN clause is an independent boolean condition.

CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2 ...] [ELSE result] END

Simple form - an input expression is compared (using =) to each WHEN value.

CASE inputExpression WHEN value1 THEN result1 [WHEN value2 THEN result2 ...] [ELSE result] END

Branches are evaluated top-to-bottom; the first matching branch's THEN expression is returned. WHEN, THEN and ELSE accept any expression - HL7 paths, arithmetic, nested CASE, function calls, etc. If no branch matches and no ELSE is provided, the result is null.

Comparison semantics for the simple form match the language's = operator: case-insensitive ordinal for strings, type-coercing for numerics and dates.

Examples

Simple form

CASE PID-8 WHEN 'F' THEN 'Female' WHEN 'M' THEN 'Male' WHEN 'O' THEN 'Other' ELSE 'Unknown' END

Searched form (arbitrary conditions per branch)

CASE WHEN PID-7 < '1953' THEN 'Senior' WHEN PID-7 < '2000' THEN 'Adult' ELSE 'Younger' END

Mixing AND/OR/IS NULL in conditions

CASE WHEN PID-8 = 'F' OR PID-8 = 'M' THEN 'Binary' WHEN PID-8 IS NULL THEN 'Missing' ELSE 'Other' END

Returning expressions, not just literals

CASE PID-8 WHEN 'F' THEN PID-5.1 + ' (F)' WHEN 'M' THEN PID-5.1 + ' (M)' ELSE PID-5.1 END

CASE vs TABLE vs IF - which to use

IF - one binary condition; cleanest for a single yes/no test.

TABLE - fast lookup with literal string keys and literal string results. Best for code-to-label translations with no logic per branch.

CASE - the most general; use when branches need conditions (searched form) or when THEN/ELSE need to be expressions, not literals.

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 anywhere 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 birthdays < 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 whose last name contains '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%'

Translate a gender code into a friendly label

SELECT MSH-9, PID-3, PID-5, TABLE(PID-8, 'F'->'Female', 'M'->'Male', 'O'->'Other', ?->'Unknown') AS Gender

Same translation using CASE (works for non-literal results too)

SELECT MSH-9, PID-3, PID-5, CASE PID-8 WHEN 'F' THEN 'Female' WHEN 'M' THEN 'Male' WHEN 'O' THEN 'Other' ELSE 'Unknown' END AS Gender

Classify patients by age band using the searched CASE form

SELECT MSH-9, PID-3, PID-5, PID-7, CASE WHEN PID-7 < '19450101' THEN 'Pre-1945' WHEN PID-7 < '19650101' THEN '1945-1964' WHEN PID-7 < '19850101' THEN '1965-1984' ELSE '1985+' END AS AgeBand WHERE PID-7 IS NOT NULL