Re: [sqlite] LIKE and the like and SIMilarity
Two decent suggestions in the replies: 1) Set the PRAGMA to case-sensitive, and then use lower() to get insensitivity. 2) Define the column to use case-sensitive collation For #1 = Set the PRAGMA. then use lower() - Is the PRAGMA for case-sensitivity sticky? IOW, does it end up in the DB? (on the road, can't check right now.) If it is, that's good, as long as the DB is R/W, which may not be the case. If it's not, then you have to kick the pragma out every time you open the DB, right? Can I assume it sticks though one DB open/close sequence? For #2 - Define the column as case-sensitive This is good for a new DB. However, the DB may not be your DB. You may not have had the opportunity to define anything. It may have been done by someone else, the DB established, created by an application, etc. You may not have the user privileges to change the R/W status of the original. The acts of defining a DB and using that DB may not be closely coupled in either time or personnel. In a case where the coupling is loose or highly disjoint, you would have to re-create the target column(s) (or the entire DB, if the original is not R/W) with the new definitions. If you don't have control over the original, that could be a significant pushup. Whereas if you could simply say the equivalent of LIKE and ILIKE, there would be no issue at all. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE and the like and SIMilarity
Here's the problem as I see it (demo SQL is lame, but makes the point):
SELECT trim(name) FROM names WHERE name LIKE('Ben') and name
ILIKE('benjamin')
...you can't do that in SqLite using a pragma, can you? If you can, I'd
sure like to learn how.
If you can't, not to belabor the point, but you *can* do it in PostgreSQL,
and while I'm not suggesting that SqLite should strive for the
sophistication of PostgreSQL, the issue of SQL programmer ability to use,
and mix, both case-sensitive and case-insensitive means is pretty basic
stuff.
If the SQL spec for LIKE is "collation of characters", fine, by all means
implement the capability another way that uses more reasonable means. I
don't care what it is called at *all*.
The lack of the *ability* really can't be defended. It's down to "how to do
it", not "why do it."
Textual data has case. Sometimes that matters. Sometimes it doesn't. A
database engine should be able to cleanly deal with that without forcing
the programmer to write custom code.
--Ben
fyng...@gmail.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LIKE and the like and SIMilarity
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
