Re: [sqlite] LIKE and the like and SIMilarity

[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.
___
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

[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-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Anony Mous
> Sent: Wednesday, 11 January, 2017 10:55
> To: sqlite-users@mailinglists.sqlite.org
> Subject: 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-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

[Jens Alfke]

> On Jan 11, 2017, at 1:34 PM, R Smith  wrote:
> 
> 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
___
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

[Simon Slavin]

On 11 Jan 2017, at 9:34pm, R Smith  wrote:

> 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.
___
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

[R Smith]

On 2017/01/11 11:19 PM, Darren Duncan wrote:

On 2017-01-11 10:11 AM, Jens Alfke wrote:
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;

___
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

[Darren Duncan]

On 2017-01-11 10:11 AM, Jens Alfke wrote:

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


___
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

[R Smith]

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

Re: [sqlite] LIKE and the like and SIMilarity

[Jens Alfke]

> On Jan 11, 2017, at 9:55 AM, Anony Mous  wrote:
> 
> 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.

>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
___
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

[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
fyng...@gmail.com
___
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

[Clemens Ladisch]

Anony Mous wrote:
> 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.

> I suggest ... that SqLite implement:
> SIM and ISIM

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


Regards,
Clemens
___
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

[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.

On Sun, Jan 8, 2017 at 4:25 PM, Anony Mous  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