Page 1 of 1
Case-insensitive LIKE in SQLite
Posted: Mon Dec 20, 2010 12:58 pm
by Technical
Is there any way to perform case-insensitive LIKE, except using user defined REGEX-function?
Re: Case-insensitive LIKE in SQLite
Posted: Mon Dec 20, 2010 1:47 pm
by s.dot
http://www.sqlite.org/lang_expr.html wrote:Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). (A bug: SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)
Re: Case-insensitive LIKE in SQLite
Posted: Mon Dec 20, 2010 2:30 pm
by Technical
s.dot wrote:http://www.sqlite.org/lang_expr.html wrote:The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)
Sorry, should mentioned that I'm using
Unicode.
Re: Case-insensitive LIKE in SQLite
Posted: Thu Dec 23, 2010 12:33 pm
by Technical
The question is still active, can someone please help me?
I need case-insensitive search with SQL query in SQLite on Unicode encoding.
Is there any solution?
Re: Case-insensitive LIKE in SQLite
Posted: Fri Dec 24, 2010 3:17 pm
by Technical
Found myself, I think making both search string and column which is used in searching to lowercase will work fine.
Re: Case-insensitive LIKE in SQLite
Posted: Sat Dec 25, 2010 2:05 pm
by josh
Sometimes you just need regex too...
Re: Case-insensitive LIKE in SQLite
Posted: Sat Dec 25, 2010 3:09 pm
by Technical
josh wrote:Sometimes you just need regex too...
Shame there is no built-in REGEX in SQLite.
Re: Case-insensitive LIKE in SQLite
Posted: Sun Dec 26, 2010 4:49 pm
by josh
Sounds like it was not a powerful enough choice for a storage engine.
Re: Case-insensitive LIKE in SQLite
Posted: Mon Dec 27, 2010 1:06 am
by Technical
josh wrote:Sounds like it was not a powerful enough choice for a storage engine.
It's fine. This is the only problem I faced for 3 years.
Re: Case-insensitive LIKE in SQLite
Posted: Mon Dec 27, 2010 2:02 am
by josh
Once again, you need regex to do this. If your current database engine does not support regex, it is probably not possible. Look into a more robust database system. The fact you've used it for 3yrs doesn't change the fact it doesn't support regex, which is needed to do what you want.