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

Re: [sqlite] LIKE and the like and SIMilarity

.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(na

Re: [sqlite] LIKE and the like and SIMilarity

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

Re: [sqlite] LIKE and the like and SIMilarity

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

Re: [sqlite] LIKE and the like and SIMilarity

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

Re: [sqlite] LIKE and the like and SIMilarity

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

Re: [sqlite] LIKE and the like and SIMilarity

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.

Re: [sqlite] LIKE and the like and SIMilarity

> 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

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

Re: [sqlite] LIKE and the like and SIMilarity

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

Re: [sqlite] LIKE and the like and SIMilarity

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