Skip to main content

String Operators

KQL string operators cover case-sensitive and case-insensitive equality, substring containment, prefix/suffix matching, word-boundary token matching, and regular expressions. The most important distinction to be aware of is that KQL contains and has are case-insensitive by default. The compiler handles this per dialect — using ILIKE where the engine provides it (ClickHouse, PostgreSQL, DuckDB), and relying on the engine's default case-insensitive collation with plain LIKE elsewhere (SQLite, MySQL).

Legend: ✅ Supported · ⚠️ Approximated · ❌ Not Supported · 🔄 Rewritten

OperatorSQLiteMySQLClickHousePostgreSQLDuckDBNotes
==✅✅✅✅✅Case-sensitive equality
!= / <>✅✅✅✅✅
=~✅✅✅✅✅lower(a) = lower(b)
!~✅✅✅✅✅lower(a) != lower(b)
contains⚠️⚠️✅✅✅KQL contains is case-insensitive. ClickHouse/Postgres/DuckDB: col ILIKE '%val%'. SQLite/MySQL: plain col LIKE '%val%' relying on default case-insensitive collation — no ILIKE
!contains⚠️⚠️✅✅✅
contains_cs✅✅✅✅✅Case-sensitive LIKE
startswith⚠️⚠️✅✅✅SQLite/MySQL: LIKE val%; Postgres/DuckDB: ILIKE val%
!startswith⚠️⚠️✅✅✅
startswith_cs✅✅✅✅✅
endswith⚠️⚠️✅✅✅SQLite/MySQL: LIKE %val; Postgres/DuckDB: ILIKE %val
!endswith⚠️⚠️✅✅✅
endswith_cs✅✅✅✅✅
has⚠️🔄✅✅⚠️KQL has uses word/token boundary semantics. ClickHouse: hasTokenCaseInsensitive; Postgres: ~* word-boundary regex; MySQL: REGEXP_LIKE word-boundary; SQLite: LIKE %val% — no token boundary, false positives possible; DuckDB: ILIKE substring — native regex is not used for has, so no token boundary (false positives possible)
!has⚠️🔄✅✅⚠️Same caveat as has
has_cs⚠️🔄✅✅⚠️SQLite/DuckDB: LIKE approximation without word boundary
has_any(list)⚠️🔄✅✅⚠️Expands to OR of has conditions
!has_any(list)⚠️🔄✅✅⚠️Expands to AND of !has conditions
has_all(list)⚠️🔄✅✅⚠️Expands to AND of has conditions
!has_all(list)⚠️🔄✅✅⚠️
hasprefix⚠️⚠️✅✅✅Mapped as startswith
hassuffix⚠️⚠️✅✅✅Mapped as endswith
matches regex⚠️🔄✅✅✅ClickHouse: match(); Postgres: ~; MySQL: REGEXP_LIKE; DuckDB: regexp_matches() (substring match); SQLite: REGEXP operator requires a user-defined REGEXP() function registered at connection time
in✅✅✅✅✅IN (...)
!in✅✅✅✅✅NOT IN (...)
in~✅✅✅✅✅Case-insensitive via lower()
!in~✅✅✅✅✅
between✅✅✅✅✅BETWEEN ... AND ...
!between✅✅✅✅✅NOT BETWEEN ... AND ...
warning

SQLite does not have a native REGEXP operator. Using matches regex against a SQLite target requires a custom REGEXP() function to be registered on the connection before the query runs. Without it, the query will fail at runtime.