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

Operator	SQLite	MySQL	ClickHouse	PostgreSQL	DuckDB	Notes
`==`	✅	✅	✅	✅	✅	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.