Search for a range of characters in SQLite-CodePudding

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'

CodePudding user response：

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.