Filter Functions

Filter functions provided by the SanteSuite community are summarized on this page. 
Core HDSI Filters
Age
The age filter is used to express a filter based on the "age" of a timestamp at a particular date. Age can be passed with no parameters (indicating age at current date) or can be used with a parameter (indicating age at specified date).
1
:(age)value
2
:(age|yyyy-MM-dd)value
Copied!
Where value is an ISO8601 duration format.
Parameter
Opt
Description
dateOfAge
Optional
The date on which the age value should be evaluated.
(return)
​
The date difference between the filter field and the input parameter in ISO8601 duration.
SQL Translations
PostgreSQL
Supported (since 2.1)
FirebirdSQL
Supported (since 2.1)
SQLite
Not Supported (see date_diff)
PostgreSQL
1
WHERE GREATEST(column::TIMESTAMP - COALESCE(@otherDate::TIMESTAMP, CURRENT_TIMESTAMP), 
2
      COALESCE(@otherDate::TIMESTAMP, CURRENT_TIMESTAMP) - column::TIMESTAMP) <operator> @value::INTERVAL
Copied!
FirebirdSQL
1
WHERE ABS(DATEDIFF(millisecond, CAST(column AS TIMESTAMP), 
2
         COLESACE(CAST(@otherDate AS TIMESTAMP), CURRENT_TIMESTAMP)) <operator> @value
Copied!
Examples
1
// Under 1 year at current date
2
dateOfBirth=:(age)<P1Y
3
// Over 2 years old on Jan 1 2022
4
dateOfBirth=:(age|2022-01-01)>P2Y
Copied!
Date Difference
The date difference function is enabled on PostgreSQL and FirebirdSQL ORM providers and require no additional configuration. The value is any HDSI operator and an ISO8601 duration.
1
:(date_diff|otherDate)value
Copied!
Parameter
Opt
Description
@otherDate
Required
The other date to compare to. This duration is translated to TimeSpan and the total seconds are used for comparison.
(return)
​
The difference between the property and the otherDate in ISO8601 duration
SQL Translations
PostgreSQL
Supported (since 2.0)
FirebirdSQL
Supported (since 2.0)
SQLite
Supported (since 2.0)
PostgreSQL
1
WHERE GREATEST(column::TIMESTAMP - @otherDate::TIMESTAMP, 
2
      @otherDate::TIMESTAMP - column::TIMESTAMP) <operator> @value::INTERVAL
Copied!
FirebirdSQL
1
WHERE ABS(DATEDIFF(millisecond, CAST(column AS TIMESTAMP), 
2
         CAST(@otherDate AS TIMESTAMP)) <operator> @value
Copied!
SQLite
1
WHERE ABS(column - @otherDate.Ticks) <operator> @value.Ticks
Copied!
Examples
1
// Born within 3 years of 1990
2
dateOfBirth=:(date_diff|1990)<3y
Copied!
Substring
Extracts a portion of a string and matches it with the provided value.
1
:(substr|start,[length])value
Copied!
Parameter
Opt
Description
start
Required
The starting position in the string
length
Optional
The length to extract
(return)
​
The extracted part of the string
SQL Translations
PostgreSQL
Supported (since 2.0)
FirebirdSQL
Supported (since 2.0)
SQLite
Supported (since 2.0)
PostgreSQL & FirebirdSQL
1
WHERE SUBSTRING(column FROM @start FOR @length) <operator>
2
       SUBSTRING(@value FROM @start FOR @length)
Copied!
SQLite
1
WHERE substr(column, @start, @length) <operator> substr(@value, @start, @length)
Copied!
Examples
1
// Identifier where the first 5 digits match 12345
2
identifier.value=:(substr|0,5)12345
Copied!
Date Truncate
Truncates a full DateTime or DateTimeOffset object to only the precision specified.
1
:(date_trunc|[yMd])value
Copied!
Parameter
Opt
Description
precision
Required
y = Year portion
M = Year + Month
d = Year + Month + Day
(return)
​
A DateTime or DateTimeOffset truncated to only the portion specified.
SQL Translations
PostgreSQL
Supported (since 2.2)
FirebirdSQL
Supported (since 2.2)
SQLite
Not Supported
PostgreSQL / FirebirdSQL 
1
WHERE column BETWEEN @minDate AND @maxDate
Copied!
@minDate and @maxDate are computed in .NET based on precision where:
Precision
@minDate
@maxDate
Year
new DateTime(value.Year, 01, 01)
new DateTime(value.Year, 12, 31)
Month
new DateTime(value.Year, value.Month, 01)
new DateTime(value.Year, value.Month, DateTime.DaysInMonth(value.Month)
Day
value.Date
value.Date
SanteMatch Filters
Approximate Match
Approximate matching is enabled when the SanteDB matcher plugin is enabled in the configuration for the dCDR or iCDR. The approximate matching function will use a combination of pattern, phonetic, and string difference functions to determine matching based on the configuration of the server running the query.
1
:(approx|otherString)
Copied!
Parameter
Opt
Description
otherString
Required
The other string to compare approximation to
(return)
​
True if the server determines the property is approximately the same as otherString
SQL Translations / Support
PostgreSQL
Supported (since 2.0)
FirebirdSQL
Supported (since 2.0)
SQLite
Not Supported
Examples
Filter for a name which sounds like, is about the same as, or minor typo's from JIHN (i.e. match JOHN, JOHNNY, JON, etc.).
1
name.component.value=:(approx|JIHN)
Copied!
Sounds Like
Uses the configured phonetic algorithm to determine whether the supplied string sounds like the stored property value. The algorithm, if not specified by the implementer, is the discretion of the implementer of the server plugin.
1
:(soundslike|otherString,[algorithm])
Copied!
Parameter
Opt
Description
otherString
Required
The other string to compare 
algorithm
Optional
Dictates the algorithm to use (soundex, metaphone, dmetaphone)
(return)
​
True if the server determines the property sounds like otherString
SQL Translations / Support
PostgreSQL
Supported (since 2.0)
FirebirdSQL
Not Supported
SQLite
Supported (since 2.0 - requires SQLite Soundex option)
Examples
To filter for patients who have a name which sounds like Tyler (i.e. match Tyler, Tiler, etc.)
1
name.component.value=:(soundexlike|TYLER)
Copied!
Phonetic Difference
The phonetic difference function is used to compare the difference in phonetic codes between two values. This function by default uses the SOUNDEX algorithm and then performs a LEVENSHTEIN function against the result.
1
:(phonetic_diff|otherString,[algorithm])value
Copied!
Parameter
Opt
Description
otherString
Required
The other string to compare 
algorithm
Optional
Dictates the algorithm to use (soundex, metaphone, dmetaphone)
(return)
​
The difference (0 - 4) in soundex codes
SQL Translations / Support
PostgreSQL
Supported (since 2.1)
FirebirdSQL
Not Supported
SQLite
Not Supported
Soundex Comparison 
The soundex comparison is used to compare the difference in SOUNDEX codes of each input.
1
:(soundex)OTHER
Copied!
SQL Translations / Support
PostgreSQL
Supported (since 2.0)
FirebirdSQL
Not Supported
SQLite
Supported (since 2.0 - requires SQLite Soundex option)
Metaphone Comparison
The metaphone comparison is used to compare the values based on their metaphone code. Metaphone filter takes an optional length specifier.
1
:(metaphone)OTHER
2
:(metaphone|3)OTHER
Copied!
SQL Translations / Support
PostgreSQL
Supported (since 2.0)
FirebirdSQL
Not Supported
SQLite
Supported (since 2.0 - requires SQLite spellfix plugin)
Double Metaphone Comparison
The double metaphone comparison is used to compare values based on the double metaphone code.
1
:(dmetaphone)OTHER
Copied!
SQL Translations / Support
PostgreSQL
Supported (since 2.0)
FirebirdSQL
Not Supported
SQLite
Not Supported
Levenshtein Difference 
The levenshtein difference is used to compute the difference in edit distance between the source and input.
1
:(levenshtein|OTHER)<3
Copied!
Using the levenshtein function has a performance penalty in that the database tables storing the values (identifiers, addresses, etc.) needs to be sequentially scanned. If you can, consider using  similarity_lev which can uses PostgreSQL's trigram index (see similarity extension).
SQL Translations / Support
PostgreSQL
Supported (since 2.0)
FirebirdSQL
Not Supported
SQLite
Supported (since 2.0 - requires SQLite spellfix plugin)
Similarity
The similarity function will use the database's string matching similarity functionality to perform an indexed match (in PostgreSQL the similarity operator used). For example, to compare similarity of given names > 0.8 from SMITH
1
name.component[Family].value=:(similarity|SMITH)>0.8
Copied!
When using PostgreSQL the :(similarity) filter function will be translated into an optimized lookup of column % 'SMITH' AND similarity(column, 'SMITH') > 0.8 . It is therefore important to properly set the default (used by the % operator) via: 
1
ALTER DATABASE santedb SET pg_trgm.word_similarity_threshold = 0.6;
Copied!
SQL Translations / Support
PostgreSQL
Supported (since 2.2)
FirebirdSQL
Not Supported
SQLite
Not Supported
Similarity + Levenshtein
The similarity_lev filter acts similar to the similarity in that it uses the underlying database technology's GIN indexing to perform a similarity, however the final result is run through the levenshtein function. 
1
identifier[SSN].value=:(similarity_lev|304-304-3049)<3
Copied!
Would be queried in PostgreSQL as:
1
WHERE id_val % '304-304-3049' AND levenshtein(id_val, '304-304-3049') < 3
Copied!
This method should be used over similarity when:
The strings being compared require an exact number of modifications to pass the filter (like identifiers accounting for type-o's)
The use case has too many values for a plain levenshtein and a pre-index of similarity is preferred.
Implementers should note that the default similarity threshold will impact what is passed to levenshtein and the database will need be tuned based on the implementation specific data. For example, if using Canadian Social Insurance Numbers, with a desired levenshtein test of 5 then it would best to set word_similarity_threshold to 0.4 since SIN numbers with 5 edits would be represent a 60% difference in source strings.
SQL Translations / Support
PostgreSQL
Supported (since 2.2)
FirebirdSQL
Not Supported
SQLite
Not Supported
Custom Filter Functions
Implementers can write custom filter functions by implementing the following interfaces:
​IQueryExtensionFilter interface which maps and composes the HDSI query expression to/from a .NET Expression tree 
Implementing a .NET Extension Method which allows the HDSI filter to be exposed in a .NET LINQ Expression (and which is used to evaluate the HDSI filter on .NET objects in memory)
Implementing IDbFilterFunction interface for each persistence interface. This class translates the HDSI Query and LINQ extension into SQL for the appropriate platform.