Case-insensitive LIKE in SQLite

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Technical
Forum Commoner
Posts: 81
Joined: Thu Dec 02, 2010 5:30 am

Case-insensitive LIKE in SQLite

Post by Technical »

Is there any way to perform case-insensitive LIKE, except using user defined REGEX-function?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Case-insensitive LIKE in SQLite

Post 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.)
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Technical
Forum Commoner
Posts: 81
Joined: Thu Dec 02, 2010 5:30 am

Re: Case-insensitive LIKE in SQLite

Post 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.
User avatar
Technical
Forum Commoner
Posts: 81
Joined: Thu Dec 02, 2010 5:30 am

Re: Case-insensitive LIKE in SQLite

Post 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?
User avatar
Technical
Forum Commoner
Posts: 81
Joined: Thu Dec 02, 2010 5:30 am

Re: Case-insensitive LIKE in SQLite

Post by Technical »

Found myself, I think making both search string and column which is used in searching to lowercase will work fine.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Case-insensitive LIKE in SQLite

Post by josh »

Sometimes you just need regex too...
User avatar
Technical
Forum Commoner
Posts: 81
Joined: Thu Dec 02, 2010 5:30 am

Re: Case-insensitive LIKE in SQLite

Post by Technical »

josh wrote:Sometimes you just need regex too...
Shame there is no built-in REGEX in SQLite.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Case-insensitive LIKE in SQLite

Post by josh »

Sounds like it was not a powerful enough choice for a storage engine.
User avatar
Technical
Forum Commoner
Posts: 81
Joined: Thu Dec 02, 2010 5:30 am

Re: Case-insensitive LIKE in SQLite

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Case-insensitive LIKE in SQLite

Post 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.
Post Reply