| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
| contains(s1, s2) | boolean | whether string s1 constains string s2. Returns null when either s1 or s2 is null | contains('abcd', 'bc') | true |
| contains(s1, s2, icase) | boolean | whether string s1 constains string s2 matching case insensitive when icase is true or null, case sensitive when icase is false. Returns null when either s1 or s2 is null | contains('abcd', 'BC', true) | true |
| endswith(s1, s2) | boolean | whether string s1 ends with string s2. Returns null when either s1 or s2 is null | endswith('abc', 'bc') | true |
| endswith(s1, s2, icase) | boolean | whether string s1 ends with string s2 matching case insensitive when icase is true or null, case sensitive when icase is false. Returns null when either s1 or s2 is null | endswith('abc', 'BC', true) | true |
| "like"(s1, s2, s3, icase) | boolean | whether string s1 matches pattern s2 using escape character s3. When icase is false matching is case sensitive, when icase is true or null matching is case insensitive. Returns null when either s1 or s2 is null | "like"('abcd','bc','\', false) | false |
| minjarowinkler(s1, s2, threshold double) | boolean | similarity distance of string s1 versus string s2 according to Jarowinkler distance computation. Returns null when either s1 or s2 is null | select minjarowinkler('abcd','bc', 0.833) | 1 |
| not_like(s1, s2, s3, icase) | boolean | whether string s1 not matches pattern s2 using escape character s3. When icase is false matching is case sensitive, when icase is true or null matching is case insensitive. Returns null when either s1 or s2 is null | not_like('abcd','bc','\', false) | true |
| startswith(s1, s2) | boolean | whether string s1 starts with string s2. Returns null when either s1 or s2 is null | startswith('bcd', 'bc') | true |
| startswith(s1, s2, icase) | boolean | whether string s1 starts with string s2 matching case insensitive when icase is true or null, case sensitive when icase is false. Returns null when either s1 or s2 is null | startswith('bcd', 'BC', true) | true |
Alternative syntax for filter functions:
select name from tables where [name] contains ['dex'];
select name from tables where [tables.name] startswith ['fun'];
select name from tables t where [t.name] endswith ['ges'];
select name from tables where name like '%ges';
select name from tables where name ilike 'Fun%';
select name from tables where name not like '%s%';
select name from tables where not name ilike '%S' escape '\\';
To list the available filter functions in your MonetDB server run query:
SELECT DISTINCT schema_id, name, func, mod, language, system FROM sys.functions WHERE type = 4 ORDER BY name;
You can also create your own filter functions, see Create Filter Function command.