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, whereas SQL LIKE is not — so non-ClickHouse dialects approximate these with LOWER() wrappers or regex rewrites.

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

Operator	SQLite	MySQL	ClickHouse	PostgreSQL	Notes
`==`	✅	✅	✅	✅	Case-sensitive equality
`!=` / `<>`	✅	✅	✅	✅
`=~`	✅	✅	✅	✅	`lower(a) = lower(b)`
`!~`	✅	✅	✅	✅	`lower(a) != lower(b)`
`contains`	⚠️	⚠️	✅	✅	KQL `contains` is case-insensitive. SQLite/MySQL: `LOWER(col) LIKE LOWER('%val%')` — no ILIKE available
`!contains`	⚠️	⚠️	✅	✅
`contains_cs`	✅	✅	✅	✅	Case-sensitive `LIKE`
`startswith`	⚠️	⚠️	✅	✅	SQLite/MySQL: `LIKE val%`
`!startswith`	⚠️	⚠️	✅	✅
`startswith_cs`	✅	✅	✅	✅
`endswith`	⚠️	⚠️	✅	✅	SQLite/MySQL: `LIKE %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
`!has`	⚠️	🔄	✅	✅	Same caveat as `has`
`has_cs`	⚠️	🔄	✅	✅	SQLite: 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`; 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.