REGEX Sqlite case-insensitive not working

Question

I can't get case insensitive searches to work for REGEX in SQLITE. Is the syntax supported?

SELECT * FROM table WHERE name REGEXP 'smith[s]*\i'

I would expect the following answers (assuming the database has these entries):

Smith
Smiths
smith
smitH <--- Not a typo but in database

Note - This is a small part of a larger REGEX, so I won't be using LIKE

Answer 1

The REGEXP function shipped with SQLite Manager is implented in JavaScript as follows:

var regExp = new RegExp(aValues.getString(0));
var strVal = new String(aValues.getString(1));

if (strVal.match(regExp)) return 1;
else return 0;

To get case-insensitive searches with the JavaScript RegExp object, you would not add a flag to the pattern string itself, but pass the i flag in the second parameter to the RegExp constructor. However, the binary REGEXP operator does not have a third flags parameter, and the code does not try to extract flags from the pattern, so these flags are not supported in this implementation.

Answer 2

As described by CL, this feature is not supported in SQLite. A simple solution to this problem is to "lowercase" the left-hand side of the REGEXP expression using lower:

SELECT * FROM table WHERE lower(name) REGEXP 'smith[s]*';

it is not ideal, but it works. But pay attention to diacritics. I would read the documentation for lower if your text uses them.