How to use "glob" operator as case insensitive

Question

I am using glob operator with "?" wildcharater.The problem is - it is case sensitive.

So suppose I want to search for "Hola", then below query does not work.

select * from tableName where columnName glob 'ho?a';

I can use LOWER or UPPER keywords with columnName , but then it also it fails for the text which is a combination of lower and upper case letters.

Please give your inputs.

See [this probably duplicate] [question](http://stackoverflow.com/questions/973541/how-to-set-sqlite3-to-be-case-insensitive-when-string-comparing) - the general tool SQL has to deal with this is COLLATIONS; also, while same-casing both values ought to "work", SQLite won't be able to use indices AFAIK — user2864740, Jan 09 '14 at 06:31

score 3 · Accepted Answer · answered Jan 09 '14 at 07:50

3

GLOB is case sensitive by design.

If you want case insensitive matching, use LIKE, with _ matching a single character:

select * from tableName where columnName like 'ho_a';

answered Jan 09 '14 at 07:50

laalto

This is really useful.It worked perfectly fine for me and simplest way to achieve the goal. – Sunita Jan 11 '14 at 18:31

score 3 · Answer 2 · answered Jan 09 '14 at 08:41

3

GLOB supports character classes:

SELECT * FROM tableName WHERE columnName GLOB '[hH][oO]?[aA]';

However, using LIKE would be easier, unless you actually need to use character classes in some other part of the pattern.

answered Jan 09 '14 at 08:41

CL.

2 Answers2