In SqLite, LIKE works backwards. It's not case-sensitive, although it should be.
It's far too late to change this now, it would break all manner of existing code. But, in more advanced engines (like PostgreSQL), LIKE is a case-sensitive match, and ILIKE is a case insensitive match. I suggest that, rather than putting in ILIKE as a case-INsensive match and thereby making even more of a mess, that SqLite implement: SIM and ISIM Where SIM is case-sensitive, and ISIM is not. So users can write better, saner queries. Or, if that's not in the cards, yes, please, by all means, put in an ILIKE that does case-sensitive matching. PLEASE. The PRAGMA is, frankly, clumsy at best. There is a non-insignificant need to be able to do both kinds of matching in one SQL statement, and without jumping through hoops, either: * Proper SQL: ------------- SELECT name FROM names WHERE trim(name) ILIKE 'ben' // case insensitive match SELECT name FROM names WHERE trim(name) LIKE 'Ben' // case sensitive match * SqLite SQL: ------------- SELECT name FROM names WHERE trim(name) LIKE 'Ben' // case INsensitive match (backwards!) * Suggestion: ------------- SELECT name FROM names WHERE trim(name) LIKE 'Ben' // case INsensitive match (backwards, remains for compatibility) SELECT name FROM names WHERE trim(name) ISIM 'ben' // NEW case insensitive match SELECT name FROM names WHERE trim(name) SIM 'Ben' // NEW case sensitive match This is the one SQL-related fundamental shortcoming that bites me time and time again. Thanks for listening, I hope this will be considered, and something like it, or exactly it, will be implemented. Ben fyng...@gmail.com _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
