Search code examples
sqlsqlitewhere-clausesql-likeglob

Search for a range of characters in SQLite

Assume the table is "t" and field is "f" and of type VARCHAR or TEXT. The SQL queries listed below work with MS Access 97. We are in the process of updating the database to SQLite3 (long overdue!), and these queries return no results.

Is this feature to match (or exclude) a range of characters available in SQLite?

  • SELECT * FROM t WHERE f LIKE '%[abcde]%'; -- match entries which contains 'a' to 'e' inclusive
  • SELECT * FROM t WHERE f LIKE '%[a-e]%'; -- same query as above
  • SELECT * FROM t WHERE f LIKE '%[^x]%'; -- match entries which do NOT contain an 'x'
Solution

  • For this you need the operator GLOB which:

    uses the Unix file globbing syntax for its wildcards

    • SELECT * FROM t WHERE f GLOB '*[abcde]*'; -- match entries which contains 'a' to 'e' inclusive
    • SELECT * FROM t WHERE f GLOB '*[a-e]*'; -- same query as above
    • SELECT * FROM t WHERE f GLOB '*[^x]*'; -- match entries which do NOT contain an 'x'

    There is also the ? wildcard which matches exactly 1 char.