[sqlite] LIKE and the like and SIMilarity

[Anony Mous]

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
***@gmail.com

[Stephen Chrzanowski]

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.

Post by Anony Mous
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
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
-------------
SELECT name FROM names WHERE trim(name) ILIKE 'ben' // case insensitive
match
SELECT name FROM names WHERE trim(name) LIKE 'Ben' // case sensitive match
-------------
SELECT name FROM names WHERE trim(name) LIKE 'Ben' // case INsensitive
match (backwards!)
-------------
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
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Clemens Ladisch]

Post by Anony Mous
In SqLite, LIKE works backwards. It's not case-sensitive, although it
should be.

The SQL standard specifies that LIKE does comparisons using the
collation of the string values.

SQLite uses NOCASE by default. You could override the like() function
(which is what PRAGMA case_sensitive_like does), but it is not possible
to get a string's collation from inside a user-defined function.

Post by Anony Mous
SIM and ISIM

This is too similar to SQL's "SIMILAR TO" operator.


Regards,
Clemens

[Anony Mous]

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
***@gmail.com

[Jens Alfke]

Post by Anony Mous
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.

+1. And while we’re at it, I’d like to see a case-insensitive string equality operator.

Post by Anony Mous
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.

But to play devil’s advocate, you _can_ do that with:
SELECT trim(name) FROM names WHERE name LIKE('Ben') and lower(name) LIKE('benjamin’)

—Jens

[Darren Duncan]

Post by Jens Alfke
And while we’re at it, I’d like to see a case-insensitive string equality operator.

Yes, that shorthand can be useful. But don't make it a pragma that overrides
the meaning of "=", which would be a world of hurt, it needs a different name.
-- Darren Duncan

[R Smith]

Post by Darren Duncan

Post by Jens Alfke
And while we’re at it, I’d like to see a case-insensitive string equality operator.

Yes, that shorthand can be useful. But don't make it a pragma that
overrides the meaning of "=", which would be a world of hurt, it needs
a different name. -- Darren Duncan

Doesn't this already do the trick?

SELECT a FROM t WHERE a = b COLLATE NOCASE;

[Simon Slavin]

Post by R Smith
Doesn't this already do the trick?
SELECT a FROM t WHERE a = b COLLATE NOCASE;

Right. Better still, if you declare the columns as COLLATE NOCASE in the first place, the comparison is done ignoring case without you having to state it in the SELECT.

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> CREATE TABLE MyTable (nameCS TEXT, nameCI TEXT COLLATE NOCASE);
sqlite> INSERT INTO MyTable VALUES ('Albert','Albert');
sqlite> INSERT INTO MyTable VALUES ('Betty','Betty');
sqlite> SELECT * FROM MyTable WHERE nameCS='betty';
sqlite> SELECT * FROM MyTable WHERE nameCI='betty';
Betty|Betty
sqlite>

Statements like "I need a case-insensitive comparison function" for a database engine worry me. They make me wonder why you didn’t define your schema correctly in the first place.

Simon.

[Jens Alfke]

Post by R Smith
SELECT a FROM t WHERE a = b COLLATE NOCASE;

D’ohh! I overlooked the COLLATE operator. Perfect.

(In my situation, these strings are not coming directly from columns, so setting collation on columns doesn’t make sense.)

—Jens

[R Smith]

Post by Anony Mous
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*".

Post by Anony Mous
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).

Post by Anony Mous
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

[Keith Medcalf]

It can. You declare the column to have a collation of NOCASE. In the case of the select you gave which does not d=contain any wildcards (and therefore is not needful of LIKE) you can pronounce:

SELECT trim(name) FROM names WHERE name = 'Ben' and name = 'benjamin' collate nocase


However since it is impossible for a string to equal both 'Ben' and 'benjamin" and the same time, even if both comparisons are case and accent insensitive, you will always get no rows returned.

-----Original Message-----
On Behalf Of Anony Mous
Sent: Wednesday, 11 January, 2017 10:55
Subject: Re: [sqlite] LIKE and the like and SIMilarity
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
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Anony Mous]

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.