On 2017/01/11 7:55 PM, Anony Mous wrote:
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.
Quite easy actually.
You can make the like be case sensitive or insensitive easily using
either compile-time options or run-time pragma like:
PRAGMA case_sensitive_like = Off;
(Also check your column collations)
Then, once the LIKE and GLOB works case-sensitive, you can easily
combine a search with case-insensitive like in this way:
SELECT TRIM(name) FROM names WHERE name LIKE 'Ben%' AND lower(name) LIKE
'benjamin';
another possibility is to leave the LIKE case insensitive and do:
SELECT TRIM(name) FROM names WHERE substr(name,3) = 'Ben' AND name LIKE
'benjamin';
Sorry if it doesn't use the exact same function as PostGres, but it
definitely CAN be done - there is no "lack of *ability*".
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.
Can PostGres fit on a phone? Can PostGres Save an entire Database in a
single file? Can PostGres access a DB without a Server?
The "LITE" in SQLite is there for a reason, and it means we do without
some of the arguably useless syntactic sugars and oversimplifications or
hand-holdy bits of SQL provided by the large client-server systems.
(Just to note - I am in no way claiming these functions to be bad or
unnecessary in the large systems, it's beautiful that they CAN do it,
but that holds no argument for it to be in SQLite).
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.
As can SQLite. And if you don't like the way SQLite can do it, then you
are even welcome to add your own user-defined-function to do so, or use
one of the myriad of off-the-shelf ones already made by other
enthusiasts - another of the beauties of SQLite - you can compile-in
almost anything. Can all the other big engines do that? (I'm looking at
you, SQL Server...)
You can also compile-in many other MATH functions, encryption functions
and a legion of other additives to suit your specific needs - but please
don't hate on us simple folk who would rather save the compiled size and
speed in lieu of a troop of functions we won't need.
(My confidence in saying the above is not because I think the ILIKE
function is necessarily a bad idea (might even add a very minimum of
code), but rather based more on the fact that this forum wasn't exactly
drowning in requests for ILIKE() functions in the last 10 years, so the
utility increase is dubious - I think this might be the first such a
request ever, though I could be wrong.)
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
