# Bug 3043: Improve ILIKE and LIKE

- Status: closed
- Project: Zero
- Creator: @aboodman
- Assignee: @arv
- Labels: zero
- Created: 2024-10-10T00:49:40Z
- Modified: 2026-06-03T20:17:21Z
- Reactions: 👍️ ×2 (AkshayPathak, MarkLyck)
- URL: https://bugs.rocicorp.dev/p/zero/issue/3043

## Description

SQLite only has LIKE which is case insensitive

- SQLite only treats ASCII cases by default
- “The ICU extension to SQLite includes an enhanced version of the LIKE operator that does case folding across all unicode characters.”

- SQLite has GLOB:
 “The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its wildcards.”

- Need to figure out if GLOB works.
    - GLOB seems to work as a replacement for LIKE.
    - **Problem**: GLOB has no way of escaping characters.
- Should we use ICU extension?
    - Yes. Without ICU support the case folding rules are very surprising.
- Should we redefine our ILIKE to only work on ASCII?
    - I don’t think this is reasonable. ILIKE would only work on ascii which is not good for non english languages.
- Should we compile sqlite with a third party reg exp engine.
    - No. I don’t think we  want to support RegExps. They can be used as denial of service attacks.
    - If we want we can implement a custom SQLite functions that support LIKE and ILIKE correctly

We also need to defined the ESCAPE char for SQLite’s LIKE operator

## Comments (2)

### @alamothe — 2025-11-14T20:32:23Z

Would be great to also add support for LIKE for numeric columns (postgresql supports this). Currently we have to duplicate our numeric IDs (we have human readable IDs on some tables because users explicitly need them, like for invoice etc.) to allow users to do typesearch for it.

### @arv — 2026-06-03T20:17:11Z

https://github.com/rocicorp/mono/pull/6098