There are pragmas to toggle SQLite to use case sensitive LIKE statements. I can't think of what it is right now (Busy with something else) but the doc does have the required statement.
I know squat about PGSql, but in MySQL and Maria, by default, LIKE is case insensitive. It might be an option between different engines. On Sun, Jan 8, 2017 at 4:25 PM, Anony Mous <fyng...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
