Re: [sqlite] LIKE IN

[Gert Van Assche]

Thanks for clarifying this, David. Learned something new today!

On Mon, 25 Nov 2019 at 15:25, David Raymond 
wrote:

> There'll be a few differences.
>
> The JOIN version will return 1 row for every item in queries which
> matches, and it will test every single one every time. So if you have in
> the queries table both 'Alex' and 'Alexand' then 'Alexander' and
> 'Alexandra' will each show up twice, once for 'Alex' and once for
> 'Alexand'. Depending on what you're doing this may be what you want.
>
> The EXISTS version will only ever return one row for each record in the
> names table, and it will stop checking other patterns once it finds one
> that matches.
>
> So if you want any info from the queries table then go with the join
> route, if you only care if yes/no there's anything at all that matches,
> then go with exists.
>
>
>
> -Original Message-
> From: sqlite-users  On
> Behalf Of Gert Van Assche
> Sent: Saturday, November 23, 2019 5:43 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] LIKE IN
>
> Both queries will work like this:
>
> DROP TABLE names;
> CREATE TABLE names (name TEXT);
> INSERT INTO names VALUES ('Alex');
> INSERT INTO names VALUES ('Alexander');
> INSERT INTO names VALUES ('Alexandra');
> INSERT INTO names VALUES ('Rob');
> INSERT INTO names VALUES ('Rhobin'); -- should not match
> INSERT INTO names VALUES ('Robert');
>
> CREATE TABLE queries (query TEXT);
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';
>
> SELECT name from names
> where exists (
> select query from queries
> where names.name like '%'||query||'%'
> );
>
>
> On Sat, 23 Nov 2019 at 11:34, Gert Van Assche  wrote:
>
> > I think this will work:
> >
> > INSERT INTO queries VALUES ('Alex');
> > INSERT INTO queries VALUES ('Rob');
> >
> > select * from names
> > where exists (
> > select query from queries
> > where names.name like '%'||query||'%'
> > );
> >
> > On Fri, 22 Nov 2019 at 15:19, David Raymond 
> > wrote:
> >
> >> Or alternatively something like:
> >>
> >> select * from table
> >> where exists (
> >> select query from queries
> >> where table.name like query
> >> );
> >>
> >> ___
> >> 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
> ___
> 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 IN

[David Raymond]

There'll be a few differences.

The JOIN version will return 1 row for every item in queries which matches, and 
it will test every single one every time. So if you have in the queries table 
both 'Alex' and 'Alexand' then 'Alexander' and 'Alexandra' will each show up 
twice, once for 'Alex' and once for 'Alexand'. Depending on what you're doing 
this may be what you want.

The EXISTS version will only ever return one row for each record in the names 
table, and it will stop checking other patterns once it finds one that matches.

So if you want any info from the queries table then go with the join route, if 
you only care if yes/no there's anything at all that matches, then go with 
exists.



-Original Message-
From: sqlite-users  On Behalf Of 
Gert Van Assche
Sent: Saturday, November 23, 2019 5:43 AM
To: SQLite mailing list 
Subject: Re: [sqlite] LIKE IN

Both queries will work like this:

DROP TABLE names;
CREATE TABLE names (name TEXT);
INSERT INTO names VALUES ('Alex');
INSERT INTO names VALUES ('Alexander');
INSERT INTO names VALUES ('Alexandra');
INSERT INTO names VALUES ('Rob');
INSERT INTO names VALUES ('Rhobin'); -- should not match
INSERT INTO names VALUES ('Robert');

CREATE TABLE queries (query TEXT);
INSERT INTO queries VALUES ('Alex');
INSERT INTO queries VALUES ('Rob');

SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';

SELECT name from names
where exists (
select query from queries
where names.name like '%'||query||'%'
);


On Sat, 23 Nov 2019 at 11:34, Gert Van Assche  wrote:

> I think this will work:
>
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> select * from names
> where exists (
> select query from queries
> where names.name like '%'||query||'%'
> );
>
> On Fri, 22 Nov 2019 at 15:19, David Raymond 
> wrote:
>
>> Or alternatively something like:
>>
>> select * from table
>> where exists (
>> select query from queries
>> where table.name like query
>> );
>>
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE IN

[Gert Van Assche]

Both queries will work like this:

DROP TABLE names;
CREATE TABLE names (name TEXT);
INSERT INTO names VALUES ('Alex');
INSERT INTO names VALUES ('Alexander');
INSERT INTO names VALUES ('Alexandra');
INSERT INTO names VALUES ('Rob');
INSERT INTO names VALUES ('Rhobin'); -- should not match
INSERT INTO names VALUES ('Robert');

CREATE TABLE queries (query TEXT);
INSERT INTO queries VALUES ('Alex');
INSERT INTO queries VALUES ('Rob');

SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';

SELECT name from names
where exists (
select query from queries
where names.name like '%'||query||'%'
);


On Sat, 23 Nov 2019 at 11:34, Gert Van Assche  wrote:

> I think this will work:
>
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> select * from names
> where exists (
> select query from queries
> where names.name like '%'||query||'%'
> );
>
> On Fri, 22 Nov 2019 at 15:19, David Raymond 
> wrote:
>
>> Or alternatively something like:
>>
>> select * from table
>> where exists (
>> select query from queries
>> where table.name like query
>> );
>>
>> ___
>> 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 IN

[Gert Van Assche]

I think this will work:

INSERT INTO queries VALUES ('Alex');
INSERT INTO queries VALUES ('Rob');

select * from names
where exists (
select query from queries
where names.name like '%'||query||'%'
);

On Fri, 22 Nov 2019 at 15:19, David Raymond 
wrote:

> Or alternatively something like:
>
> select * from table
> where exists (
> select query from queries
> where table.name like query
> );
>
> ___
> 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 IN

[David Raymond]

Or alternatively something like:

select * from table
where exists (
select query from queries
where table.name like query
);

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE IN

[Simon Davies]

Hi,

On Fri, 22 Nov 2019 at 13:18, Hamish Allan  wrote:
>
> Hi,
>
> Is it possible to achieve the effect of combining the LIKE and IN operators?
>
> So for instance if I have tables:
>
> CREATE TABLE names (name TEXT);
> INSERT INTO names VALUES ('Alexandra');
> INSERT INTO names VALUES ('Rob');
>
> CREATE TABLE matches (match TEXT);
> INSERT INTO matches VALUES ('Alex');
> INSERT INTO matches VALUES ('Alexander');
> INSERT INTO matches VALUES ('Alexandra');
> INSERT INTO matches VALUES ('Rob');
> INSERT INTO matches VALUES ('Robin');
> INSERT INTO matches VALUES ('Robert');
>
> I can query as follows:
>
> SELECT * FROM names WHERE name IN (SELECT * FROM matches);
>
> But can I do something more like:
>
> CREATE TABLE queries (query TEXT);
> INSERT INTO queries VALUES ('Alex%*');
> INSERT INTO queries VALUES ('Rob%*');
>
> SELECT * FROM table WHERE name LIKE IN (SELECT * FROM queries);
>
> Thanks,
> Hamish

Will a JOIN not do what you want?

SELECT table.* from table t JOIN queries q ON t.name LIKE q.query;

(after cleaning up query, to 'Alex%' and 'Rob%')

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE optimization when the LHS is an indexed expression?

[Keith Medcalf]

IF the LIKE optimization applied where the LHS of the like operator were an 
expression, then the index on that expression would have to have the 
appropriate collation.  In otherwords for a case_insensitive_like (the default) 
the index would have to be collate nocase in order to be useable, and for 
case_sensitive_like the index would have to be collate binary in order to be 
useable.

You can test this out quite easily:

create table x (x text collate nocase unique);
create index xn1 on x ('Yahoo' || x collate nocase);
create index xn2 on x (('Yahoo' || x) collate nocase);
create index xb1 on x ('Yahoo' || x collate binary);
create index xb2 on x (('Yahoo' || x) collate binary);

insert into x values ('A'),('b'),('C'),('d');

.eqp on
select * from x where 'Yahoo' || x like 'yahooc';

and see what index is used.  My sqlite 3.30 uses index xn2 and will not use 
index xn1 if you drop index xn2 indicating the appropriate format to apply a 
collation to an index on an expression (that is that the collate operator binds 
more tightly than the || operator so therefore the expression must be in 
parenthesis.

If you turn on case_sensitive_like, then index xb2 is used for the case 
sensitive operation and will not use index xb1 even if index xb2 is deleted.


>-Original Message-
>From: sqlite-users  On
>Behalf Of Jens Alfke
>Sent: Friday, 27 September, 2019 15:09
>To: SQLite mailing list 
>Subject: [sqlite] LIKE optimization when the LHS is an indexed
>expression?
>
>I've been reading about the LIKE optimization[1]. One of the constraints
>on its use is:
>
>> if case_sensitive_like mode is enabled then the column must indexed
>using BINARY collating sequence, or if case_sensitive_like mode is
>disabled then the column must indexed using built-in NOCASE collating
>sequence.
>
>Does this also apply when the LHS is not a column but an expression? I.e.
>does the index on that expression need to have BINARY (or NOCASE)
>collation?
>
>—Jens
>
>[1]: https://sqlite.org/optoverview.html#the_like_optimization
>___
>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 Query with ESCAPE character

[Richard Hipp]

On 2/27/19, julian robichaux  wrote:
> Am I doing something wrong here, or perhaps misunderstanding the
> documentation? My expectation is that both LIKE queries will use the
> index, but the EXPLAIN QUERY PLAN results tell me something different.

There was an issue with the LIKE optimization when there was an ESCAPE
clause and the PRAGMA case_sensitive_like=ON setting was in effect.
That particular combination of circumstances should work, but it did
not.  All the other combinations are fine.

The fix is here: https://www.sqlite.org/src/info/6ae4b8c525f446dd

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Shawn Wagner]

Look into using the ICU extension. If you're compiling sqlite yourself,
just define SQLITE_ENABLE_ICU to 1 (And link with the ICU libraries),
otherwise you'll have to grab the source and compile it as a loadable
module.

https://www3.sqlite.org/cgi/src/dir?ci=03c4f00317233a34=ext/icu for
details. (Is there a way to link to the latest version of a file in the
repository and not a particular commit?)

On Fri, Feb 15, 2019 at 6:06 AM Aydin Ozgur Yagmur 
wrote:

> Hello,
>
> I want to use custom collations for "like" and "not equals" queries.
> *select * from tbl_internal where col_internal like 'ç%' collate
> TURKISH_CI;*
>
> it is ok for "equals" operator.
> *select * from tbl_internal where col_internal = 'çç' collate TURKISH_CI;*
>
> but not ok, for "like" and "not equals" operators
>
> How can i make a search like these? Could you give me any clues?
>
> Thanks for advance,
> ___
> 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 operator and collations

[Simon Slavin]

You can write your own LIKE function and use that:



If you have the source for "collate TURKISH_CI" then you might be able to use 
it in your own function.

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

[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

Re: [sqlite] like operator

[Igor Tandetnik]

On 12/5/2016 3:43 PM, Don V Nielsen wrote:

Igor, I'm not sure if you gain anything from"length(lower(name))". Just
"length(name)" would suffice.


I'm guarding against various Unicode weirdnesses that could cause string 
length to change on case transformation. While SQLite only folds ASCII 
letters by default, it could be compiled with full ICU collation support.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like operator

[Don V Nielsen]

Igor, I'm not sure if you gain anything from"length(lower(name))". Just
"length(name)" would suffice.

On Mon, Dec 5, 2016 at 10:11 AM, Dominique Devienne 
wrote:

> On Mon, Dec 5, 2016 at 4:24 PM, Igor Tandetnik  wrote:
>
> > On 12/5/2016 10:19 AM, Igor Tandetnik wrote:
> >
> >> On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote:
> >>
> >>> select name from employee table where name like '%Araya%' or name like
> >>> '%Amul%' or name like '%Aj%';
> >>>
> >>> Table - Employee
> >>>
> >>> Id | Name | age |
> >>> 1  | Arayan Kuma | 29  |
> >>> 2  | Amul Kanth  | 30  |
> >>> 3  | Ajay Kumar | 45  |
> >>>
> >>> I dont like to use may or conditions for pattern matching using like
> >>> operator.
> >>> Is there any other way I can workaround without using or condition in
> >>> like operator in sqlite.
> >>>
> >>
> >> WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul', ''),
> >> 'Aj', '')) != length(name)
> >>
> >
> > Actually, this is not quite the same: it's case-sensitive, whereas LIKE
> is
> > case-insensitive by default. To be equivalent, make it
> >
> > WHERE length(replace(replace(replace(lower(name), 'araya', ''), 'amul',
> > ''), 'aj', '')) != length(lower(name))
>
>
> Or use the pragma [1].
> Thanks, didn't realize/know LIKE was case-insensitive (for ASCII chars
> only) by default, in SQLite.
> Also made me double-check whether Oracle is case-sensitive or not (it is)
> [2]
>
> BTW, Igor: wow :)
> Not that I'd use that ever, but still, very clever! --DD
>
> [1] https://www.sqlite.org/pragma.html#pragma_case_sensitive_like
> [2]
> http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-
> oracle
> ___
> 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 operator

[Dominique Devienne]

On Mon, Dec 5, 2016 at 4:24 PM, Igor Tandetnik  wrote:

> On 12/5/2016 10:19 AM, Igor Tandetnik wrote:
>
>> On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote:
>>
>>> select name from employee table where name like '%Araya%' or name like
>>> '%Amul%' or name like '%Aj%';
>>>
>>> Table - Employee
>>>
>>> Id | Name | age |
>>> 1  | Arayan Kuma | 29  |
>>> 2  | Amul Kanth  | 30  |
>>> 3  | Ajay Kumar | 45  |
>>>
>>> I dont like to use may or conditions for pattern matching using like
>>> operator.
>>> Is there any other way I can workaround without using or condition in
>>> like operator in sqlite.
>>>
>>
>> WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul', ''),
>> 'Aj', '')) != length(name)
>>
>
> Actually, this is not quite the same: it's case-sensitive, whereas LIKE is
> case-insensitive by default. To be equivalent, make it
>
> WHERE length(replace(replace(replace(lower(name), 'araya', ''), 'amul',
> ''), 'aj', '')) != length(lower(name))


Or use the pragma [1].
Thanks, didn't realize/know LIKE was case-insensitive (for ASCII chars
only) by default, in SQLite.
Also made me double-check whether Oracle is case-sensitive or not (it is)
[2]

BTW, Igor: wow :)
Not that I'd use that ever, but still, very clever! --DD

[1] https://www.sqlite.org/pragma.html#pragma_case_sensitive_like
[2]
http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like operator

[Igor Tandetnik]

On 12/5/2016 10:19 AM, Igor Tandetnik wrote:

On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote:

select name from employee table where name like '%Araya%' or name like
'%Amul%' or name like '%Aj%';

Table - Employee

Id | Name | age |
1  | Arayan Kuma | 29  |
2  | Amul Kanth  | 30  |
3  | Ajay Kumar | 45  |

I dont like to use may or conditions for pattern matching using like
operator.
Is there any other way I can workaround without using or condition in
like operator in sqlite.


WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul', ''),
'Aj', '')) != length(name)


Actually, this is not quite the same: it's case-sensitive, whereas LIKE 
is case-insensitive by default. To be equivalent, make it


WHERE length(replace(replace(replace(lower(name), 'araya', ''), 'amul', 
''), 'aj', '')) != length(lower(name))


--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like operator

[Igor Tandetnik]

On 12/5/2016 7:30 AM, 
ravi.shan...@cellworksgroup.com wrote:

select name from employee table where name like '%Araya%' or name like
'%Amul%' or name like '%Aj%';

Table - Employee

Id | Name | age |
1  | Arayan Kuma | 29  |
2  | Amul Kanth  | 30  |
3  | Ajay Kumar | 45  |

I dont like to use may or conditions for pattern matching using like
operator.
Is there any other way I can workaround without using or condition in
like operator in sqlite.


WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul', ''), 
'Aj', '')) != length(name)


--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Scott Robison]

On Jan 16, 2015 8:05 AM, "Simon Slavin"  wrote:
>
>
> On 16 Jan 2015, at 12:23pm, Jay Kreibich  wrote:
>
> > They can all be (re)defined, some just happen to have default functions:
> >
> > https://www.sqlite.org/lang_expr.html#like
>
> Might be worth noting here that there can be a danger in replacing the
definitions of default functions.

Excellent points. As I said earlier, I am not planning to change anything,
just was curious based on my flawed inference from the core functions page.
I am playing with some C++ code to help provide some compile time checking
of queries that isn't possible with plain C string literal based queries,
and was curious about the functions vs the operators.

SDR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Simon Slavin]

On 16 Jan 2015, at 12:23pm, Jay Kreibich  wrote:

> They can all be (re)defined, some just happen to have default functions:
> 
> https://www.sqlite.org/lang_expr.html#like

Might be worth noting here that there can be a danger in replacing the 
definitions of default functions.  These functions might be used in your schema 
(e.g. for CHECK constraints) and may be overridden in your program before it 
handles data.  Someone could then open the same database in another program 
(e.g. the Shell Tool) which had instead the default functions and use that to 
add data.

If instead you define custom functions and use those in your schema, if someone 
opens your database in another program it will have no definition for the 
function, and produce an error.

I'm not saying that overriding functions is always bad, just reminding people 
of the danger.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Jay Kreibich]

On Jan 16, 2015, at 6:56 AM, Richard Hipp  wrote:

> On 1/16/15, Scott Robison  wrote:
>> LIKE & GLOB can be overridden with user defined functions. According to
>> https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument
>> function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP
>> can be redefined.
> 
> Where did you read that MATCH and REGEXP could not be redefined?  That
> is a bug in the documentation that needs to be fixed.  They are
> undefined by default and are given meaning by redefining.  They would
> be useless if they were not redefinable.
> 


I think the OP interpreted the lack of a match() or regexp() function on the 
lang_corefunc.html page as meaning they could not be defined/redefined.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Scott Robison]

On Fri, Jan 16, 2015 at 5:56 AM, Richard Hipp  wrote:

> On 1/16/15, Scott Robison  wrote:
> > LIKE & GLOB can be overridden with user defined functions. According to
> > https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument
> > function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP
> > can be redefined.
>
> Where did you read that MATCH and REGEXP could not be redefined?  That
> is a bug in the documentation that needs to be fixed.  They are
> undefined by default and are given meaning by redefining.  They would
> be useless if they were not redefinable.
>

I was browsing the list of functions at
https://www.sqlite.org/lang_corefunc.html and inferred from the lack of
REGEXP or MATCH functions (since they are effectively undefined) that their
presence would not impact the REGEXP or MATCH operators.

In like fashion, the GLOB function only documents a two argument form, not
a three argument form.

-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Richard Hipp]

On 1/16/15, Scott Robison  wrote:
> So the only remaining question is whether there is any functional
> difference between the LIKE & GLOB SQL functions and the same named
> operators (other than argument order)? Is there a reason to prefer one or
> the other in SQL syntax?

They are aliases for one another.  Syntactic sugar.  You can see this
by using EXPLAIN:

   .explain
   EXPLAIN SELECT * FROM sqlite_master WHERE name LIKE 'abc%';
   EXPLAIN SELECT * FROM sqlite_master WHERE like('abc%',name);

Both generate identical bytecode.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Richard Hipp]

On 1/16/15, Scott Robison  wrote:
> LIKE & GLOB can be overridden with user defined functions. According to
> https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument
> function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP
> can be redefined.

Where did you read that MATCH and REGEXP could not be redefined?  That
is a bug in the documentation that needs to be fixed.  They are
undefined by default and are given meaning by redefining.  They would
be useless if they were not redefinable.

>
> MATCH is only used in FTS queries if my understanding is correct, so I can
> appreciate why MATCH can't be redefined given the close integration between
> the keyword and a specific virtual table module.
>
> That leaves LIKE & GLOB & REGEXP. Why the differences in redefinition
> capabilities? Why can't an ESCAPE clause form of GLOB be redefined? Why
> can't any form of REGEXP be redefined? Or are some or all of these
> "exceptions" possible but the documentation doesn't cover them?
>
> I have no desire to redefine anything myself, this is primarily an exercise
> in understanding why the design choices were made, and I am wondering if
> there is ever a reason in SQL code to choose the function form of the LIKE
> (or other) operator over the operator itself. Is it intended that the
> operator form of the expression will always behave exactly like the
> function form of the expression (with the appropriate reordering of
> arguments)?
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Scott Robison]

On Fri, Jan 16, 2015 at 5:23 AM, Jay Kreibich  wrote:

>
> On Jan 16, 2015, at 5:06 AM, Scott Robison 
> wrote:
>
> > LIKE & GLOB can be overridden with user defined functions. According to
> > https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument
> > function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP
> > can be redefined.
>
> They can all be (re)defined, some just happen to have default functions:
>
> https://www.sqlite.org/lang_expr.html#like


Thanks for the link. I was so focused on the syntax at the top of the page
and the function lists that I never scrolled down far enough, obviously.

So the only remaining question is whether there is any functional
difference between the LIKE & GLOB SQL functions and the same named
operators (other than argument order)? Is there a reason to prefer one or
the other in SQL syntax?

-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Jay Kreibich]

On Jan 16, 2015, at 5:06 AM, Scott Robison  wrote:

> LIKE & GLOB can be overridden with user defined functions. According to
> https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument
> function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP
> can be redefined.

They can all be (re)defined, some just happen to have default functions:

https://www.sqlite.org/lang_expr.html#like


  -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like and percent character

[RSmith]

On 2014/07/03 17:58, Micka wrote:

Sorry every one ! It's my fault !

I made a function on top of sqlite3_exec which have :

va_start(argp,acSql);
vasprintf(, acSql, argp);

So this one tried to identify the %A  .


my bad ^^


All good :)

This is why I was asking for your code in an earlier post, because it often happens that some other function interjects at some 
point. So for your next question - and to the benefit of those in the audience who may have next questions - With great frequency on 
this list we get:


"Why is SQLite/sqlite3.exe doing xxx and in my program it does yyy???"

which then turns out to be a long to-and-fro of "did you check zzz?" or "try www and see what happens" until at some point a eureka 
occurs which results in "Oh no I was doing this seemingly un-related thing uuu which caused the yyy, fixed now - my bad!".


It's always a learning experience and I would propose that all learning is valuable. It is hard to fathom a type of code or use that 
/none/ of the programmers on this list ever encountered before, so with the next question, try to post as thorrough as possible with 
code and the like, it helps remarkably much and we'll be delighted to help figure it out.


Have a great day!
Ryan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like and percent character

[Simon Slavin]

On 3 Jul 2014, at 4:58pm, Micka  wrote:

> my bad ^^

No problem.  Glad you figured it out.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like and percent character

[Micka]

Sorry every one ! It's my fault !

I made a function on top of sqlite3_exec which have :

va_start(argp,acSql);
vasprintf(, acSql, argp);

So this one tried to identify the %A  .


my bad ^^




On Thu, Jul 3, 2014 at 5:40 PM, Simon Slavin  wrote:

>
> > On 3 Jul 2014, at 2:38pm, RSmith  wrote:
> >
> >
> > On 2014/07/03 15:12, Micka wrote:
> >> It's really weird that :
> >>
> >> SELECT * FROM names where name LIKE '%mic%'
> >>
> >> works with the sqlite3 command shell but not with the C librairie ..
>
> It works fine with the C library.  I'm betting that you are using a C
> function to assemble the string, and your C function understands '%' as a
> formatting or escape character.
>
> Try writing code to put the string
>
> SELECT * FROM names where name LIKE '%mic%'
>
> together then print the length of the string.  It should be 43 characters
> long.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like and percent character

[Simon Slavin]

> On 3 Jul 2014, at 2:38pm, RSmith  wrote:
> 
> 
> On 2014/07/03 15:12, Micka wrote:
>> It's really weird that :
>> 
>> SELECT * FROM names where name LIKE '%mic%'
>> 
>> works with the sqlite3 command shell but not with the C librairie ..

It works fine with the C library.  I'm betting that you are using a C function 
to assemble the string, and your C function understands '%' as a formatting or 
escape character.

Try writing code to put the string

SELECT * FROM names where name LIKE '%mic%'

together then print the length of the string.  It should be 43 characters long.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like and percent character

[RSmith]

On 2014/07/03 15:12, Micka wrote:

It's really weird that :

SELECT * FROM names where name LIKE '%mic%'

works with the sqlite3 command shell but not with the C librairie ..



Really?

So if you simply make the string  testSQL = "SELECT * FROM names WHERE name LIKE 
'%mic%';"
and pass it to your SQSLite C interface, then it returns (or not) different results to the same db being connected in the same way 
via the sqlite3.exe???


If that is the case then you are using the worst C library in history and should change it immediately - but I think the C library 
is fine and you are missing something obvious. It's hard to guess what since your descriptions are frustratingly scant - maybe post 
the actual complete C code for at least the full function where the error happens?


Also, check that you are referring the exact same DB file, what do other queries return when you add stuff via the command line 
function and then refer them in the C version? Sometimes Windows will make your app refer to a file that it duplicated to another 
place (via the UAC) when it is in a protected folder (such as "Program Files") and do not have a trustable manifest.


There are sometimes slight differences in how C libraries handle certain eventualities viz. the command-line interface, but SQL is a 
form of Algebra really and the truth of the returned answer can never be compromised.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like and percent character

[Micka]

It's really weird that :

SELECT * FROM names where name LIKE '%mic%'

works with the sqlite3 command shell but not with the C librairie ..



On Thu, Jul 3, 2014 at 2:19 PM, Micka  wrote:

> Well,
>
> I would love that to be simple . but when I printf my query, I got :
>
> SELECT * FROM names where name LIKE '%mic%'
>
> that the code :
> sprintf( acQuery, "SELECT * FROM names where name  LIKE '%%%s%%'  ",
> acName);
>
>
> So what could it be ? any ideas ?
>
>
> On Tue, Jun 3, 2014 at 4:21 PM, Hick Gunter  wrote:
>
>> Probably you are using a variant of the printf() function to generate
>> your statement and it is interpreting the %m as strerror(errno)  (see man 3
>> printf), whereas it is ignoring %' (thousands separator for decimal
>> conversions) either because it does not support this conversion or it is
>> missing the conversion specifier.
>>
>> Try inserting (3,'icka') into your table.
>>
>> If it works in the shell but not in your program, then it is nearly
>> always your program that is to blame.
>>
>> -Ursprüngliche Nachricht-
>> Von: Micka [mailto:mickamus...@gmail.com]
>> Gesendet: Dienstag, 03. Juni 2014 08:58
>> An: sqlite-users@sqlite.org
>> Betreff: [sqlite] Like and percent character
>>
>> Hi,
>>
>> I'm having trouble with the percent character .
>>
>>
>> By example in my table I have :
>>
>> id name
>> 1 micka
>> 2 mickael
>>
>> I would like to do that :
>>
>> Select * from table name where name LIKE '%micka%'
>>
>> with my linux c program, the result is 0
>>
>> but with the sqlite3 command program it works 
>>
>> I also tested this :
>>
>> Select * from table name where name LIKE 'micka%'
>>
>> and this time, it works in my linux c program ...
>>
>> I'm using the last package of sqlite3-dev ...
>> https://packages.debian.org/wheezy/sqlite3
>>
>> Why ?
>>
>> Thx you !
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ---
>> Gunter Hick
>> Software Engineer
>>
>> Scientific Games International GmbH
>> Klitschgasse 2 – 4, A - 1130 Vienna,
>> Austria
>> FN 157284 a, HG Wien
>> Tel: +43 1 80100 0
>> E-Mail: h...@scigames.at
>>
>> This e-mail is confidential and may well also be legally privileged. If
>> you have received it in error, you are on notice as to its status and
>> accordingly please notify us immediately by reply e-mail and then
>> delete this message from your system. Please do not copy it or use it for
>> any purposes, or disclose its contents to any person as to do so could be a
>> breach of confidence. Thank you for your cooperation.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like and percent character

[Micka]

Well,

I would love that to be simple . but when I printf my query, I got :

SELECT * FROM names where name LIKE '%mic%'

that the code :
sprintf( acQuery, "SELECT * FROM names where name  LIKE '%%%s%%'  ",
acName);


So what could it be ? any ideas ?


On Tue, Jun 3, 2014 at 4:21 PM, Hick Gunter  wrote:

> Probably you are using a variant of the printf() function to generate your
> statement and it is interpreting the %m as strerror(errno)  (see man 3
> printf), whereas it is ignoring %' (thousands separator for decimal
> conversions) either because it does not support this conversion or it is
> missing the conversion specifier.
>
> Try inserting (3,'icka') into your table.
>
> If it works in the shell but not in your program, then it is nearly always
> your program that is to blame.
>
> -Ursprüngliche Nachricht-
> Von: Micka [mailto:mickamus...@gmail.com]
> Gesendet: Dienstag, 03. Juni 2014 08:58
> An: sqlite-users@sqlite.org
> Betreff: [sqlite] Like and percent character
>
> Hi,
>
> I'm having trouble with the percent character .
>
>
> By example in my table I have :
>
> id name
> 1 micka
> 2 mickael
>
> I would like to do that :
>
> Select * from table name where name LIKE '%micka%'
>
> with my linux c program, the result is 0
>
> but with the sqlite3 command program it works 
>
> I also tested this :
>
> Select * from table name where name LIKE 'micka%'
>
> and this time, it works in my linux c program ...
>
> I'm using the last package of sqlite3-dev ...
> https://packages.debian.org/wheezy/sqlite3
>
> Why ?
>
> Thx you !
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ---
> Gunter Hick
> Software Engineer
>
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna,
> Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This e-mail is confidential and may well also be legally privileged. If
> you have received it in error, you are on notice as to its status and
> accordingly please notify us immediately by reply e-mail and then
> delete this message from your system. Please do not copy it or use it for
> any purposes, or disclose its contents to any person as to do so could be a
> breach of confidence. Thank you for your cooperation.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like and percent character

[Kevin Martin]

Are you forming your query with sprintf? It may be worth printing the query you 
are preparing, to make sure it says what you think it is.

Thanks,
Kev

Sent from my iPhone

> On 3 Jun 2014, at 14:53, Micka  wrote:
> 
> Hi,
> 
> I'm having trouble with the percent character .
> 
> 
> By example in my table I have :
> 
> id name
> 1 micka
> 2 mickael
> 
> I would like to do that :
> 
> Select * from table name where name LIKE '%micka%'
> 
> with my linux c program, the result is 0
> 
> but with the sqlite3 command program it works 
> 
> I also tested this :
> 
> Select * from table name where name LIKE 'micka%'
> 
> and this time, it works in my linux c program ...
> 
> Why ?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like and percent character

[Hick Gunter]

Probably you are using a variant of the printf() function to generate your 
statement and it is interpreting the %m as strerror(errno)  (see man 3 printf), 
whereas it is ignoring %' (thousands separator for decimal conversions) either 
because it does not support this conversion or it is missing the conversion 
specifier.

Try inserting (3,'icka') into your table.

If it works in the shell but not in your program, then it is nearly always your 
program that is to blame.

-Ursprüngliche Nachricht-
Von: Micka [mailto:mickamus...@gmail.com]
Gesendet: Dienstag, 03. Juni 2014 08:58
An: sqlite-users@sqlite.org
Betreff: [sqlite] Like and percent character

Hi,

I'm having trouble with the percent character .


By example in my table I have :

id name
1 micka
2 mickael

I would like to do that :

Select * from table name where name LIKE '%micka%'

with my linux c program, the result is 0

but with the sqlite3 command program it works 

I also tested this :

Select * from table name where name LIKE 'micka%'

and this time, it works in my linux c program ...

I'm using the last package of sqlite3-dev ...
https://packages.debian.org/wheezy/sqlite3

Why ?

Thx you !
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Wed, May 14, 2014 at 1:35 PM, Jan Slodicka  wrote:

> Simon Slavin-3 wrote
> > On 13 May 2014, at 5:21pm, Constantine Yannakopoulos wrote:
> >
> >> ​This is very interesting Jan. The only way this could fail is if the
> >> collation implementation does something funny if it encounters this
> >> character​, e.g. choose to ignore it when comparing.
> >
> > That cuts out a very large number of collations.  The solution works fine
> > for any collation which orders strings according to Unicode order.  But
> > the point of creating a correlation is that you don't want that order.
> >
> > Simon.
>
> Simon, I think that the most frequent point of making a collation is to get
> the Unicode order. At the bare minimum adding LIKE optimization to the ICU
> Sqlite extension would make sense, the savings are really huge.
>

There could be a flag in sqlite3_create_collation_v2()'s TextRep argument,
much like the flag SQLITE_DETERMINISTIC of sqlite3_create_function() that
will flag the collation as a "unicode text" collation. If this flag is set,
the engine can perform the LIKE optimization for these collations using the
U+10FFFD idea to construct an upper limit for the range as it has been
described in previous posts. Since this flag is not present in existing
calls to sqlite3_create_collation_v2() the change will be
backward-compatible.

Either this or the already mentioned idea of giving the ability to manually
specify lower and upper bounds for the LIKE optimization, perhaps by means
of a callback in a hypothetical sqlite3_create_collation_v3() variant.

And by the way, "unicode text" collations include all "strange" collations
like the one of accent insensitivity and mixed codepage I described in my
original post. And I would expect these to be about 95% of all custom coded
collations.

--Constantine
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Jan Slodicka]

Simon Slavin-3 wrote
> On 13 May 2014, at 5:21pm, Constantine Yannakopoulos wrote:
> 
>> ​This is very interesting Jan. The only way this could fail is if the
>> collation implementation does something funny if it encounters this
>> character​, e.g. choose to ignore it when comparing.
> 
> That cuts out a very large number of collations.  The solution works fine
> for any collation which orders strings according to Unicode order.  But
> the point of creating a correlation is that you don't want that order. 
> 
> Simon.

Simon, I think that the most frequent point of making a collation is to get
the Unicode order. At the bare minimum adding LIKE optimization to the ICU
Sqlite extension would make sense, the savings are really huge.





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75667.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Clemens Ladisch]

Hick Gunter wrote:
> I was under the impression you wanted to achieve this:
>
> > select hex('abc' || X'10FFFD');
> 61626310FFFD
> > select length('abc' || X'10FFFD');
> 6

If you want to create characters through a blob, you have to use
the correct UTF-8 encoding:

 sqlite> select quote(cast(char(1114109) as blob));
 X'F48FBFBD'
 sqlite> select unicode(x'F48FBFBD');
 1114109
 sqlite> select hex('abc' || x'F48FBFBD');
 616263F48FBFBD
 sqlite> select length('abc' || x'F48FBFBD');
 4


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Jan Slodicka]

Dominique Devienne wrote
> On Tue, May 13, 2014 at 5:50 PM, Jan Slodicka 

> jano@

>  wrote:
>> So one could replace "LIKE 'xxx%'" by "BETWEEN('xxx', 'xxx' +
>> '\uDBFF\uDFFD').
> 
> make that
> 
> BETWEEN('xxx', 'xxx' + char(1114109))
> 
> I don't think SQlite supports \u literals, nor does it support hex
> literals, so must use the decimal equivalent to U+10FFFD.

Dominique,

I just wanted to sketch the idea and did not care of the syntax. I am going
to apply this differently, anyway:-)




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75665.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Hick Gunter]

I was under the impression you wanted to achieve this:

asql> select hex('abc' || X'10FFFD');
hex('abc' || X'10FFFD')
---
61626310FFFD
asql> select length('abc' || X'10FFFD');
length('abc' || X'10FFFD')
--
6
asql> select typeof('abc' || X'10FFFD');
typeof('abc' || X'10FFFD')
--
text


-Ursprüngliche Nachricht-
Von: Dominique Devienne [mailto:ddevie...@gmail.com]
Gesendet: Mittwoch, 14. Mai 2014 09:28
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] LIKE operator and collations

On Wed, May 14, 2014 at 8:30 AM, Hick Gunter <h...@scigames.at> wrote:
> Actually SQLite does support X'...' literals for creating blobs.

Note sure how that's relevant Hick. We don't need a blob, but a integer for 
char(). I was obviously talking about *number* literals (prefixed with 0b, 0, 
0x for binary / octal / hexa), not blob literals.

I'd be +1 to having char() also accept blobs (1 to 3 bytes long) instead of 
integer, since x'10FFFD' is closer to U+10FFFD than 1114109 is.

But Dr. Hipp would need to agree with that. Until then, 1114109 it must be. --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select char(x'10FFFD');

sqlite> select typeof(char(x'10FFFD'));
text
sqlite> select length(char(x'10FFFD'));
0
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Dominique Devienne]

On Wed, May 14, 2014 at 8:30 AM, Hick Gunter  wrote:
> Actually SQLite does support X'...' literals for creating blobs.

Note sure how that's relevant Hick. We don't need a blob, but a
integer for char(). I was obviously talking about *number* literals
(prefixed with 0b, 0, 0x for binary / octal / hexa), not blob
literals.

I'd be +1 to having char() also accept blobs (1 to 3 bytes long)
instead of integer, since x'10FFFD' is closer to U+10FFFD than 1114109
is.

But Dr. Hipp would need to agree with that. Until then, 1114109 it must be. --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select char(x'10FFFD');

sqlite> select typeof(char(x'10FFFD'));
text
sqlite> select length(char(x'10FFFD'));
0
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Hick Gunter]

Actually SQLite does support X'...' literals for creating blobs.

-Ursprüngliche Nachricht-
Von: Dominique Devienne [mailto:ddevie...@gmail.com]
Gesendet: Dienstag, 13. Mai 2014 18:19
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] LIKE operator and collations

On Tue, May 13, 2014 at 5:50 PM, Jan Slodicka <j...@resco.net> wrote:
> So one could replace "LIKE 'xxx%'" by "BETWEEN('xxx', 'xxx' + '\uDBFF\uDFFD').

make that

BETWEEN('xxx', 'xxx' + char(1114109))

I don't think SQlite supports \u literals, nor does it support hex 
literals, so must use the decimal equivalent to U+10FFFD.

 C:\Users\DDevienne>sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select '\uDBFF\uDFFD';
\uDBFF\uDFFD
sqlite> select char(1114109);
􏿽
sqlite> select char(0x10FFFD);
Error: unrecognized token: "0x10FFFD"
sqlite> select typeof(char(1114109));
text
sqlite> select length(char(1114109));
1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Simon Slavin]

On 13 May 2014, at 5:21pm, Constantine Yannakopoulos wrote:

> ​This is very interesting Jan. The only way this could fail is if the
> collation implementation does something funny if it encounters this
> character​, e.g. choose to ignore it when comparing.

That cuts out a very large number of collations.  The solution works fine for 
any collation which orders strings according to Unicode order.  But the point 
of creating a correlation is that you don't want that order.  For instance, I 
have a correlation which is used for IP addresses and expects its input to look 
like

d.d.d.d

where each 'd' is one or more digits.  If you hand it a string which doesn't 
conform (octet missing, or octet > 255), that string evaluates the same as 
'0.0.0.0'.  If I was to use

... WHERE source LIKE '10.%'

and the char(1114109) solution was used I'd get incorrect results whereas

... WHERE source BETWEEN '10.0.0.0' AND '10.255.255.255'

works fine.

There isn't a solution to the problem unless SQLite has further information 
from whoever devised the collation.

Simon.

Refs: 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Dominique Devienne]

On Tue, May 13, 2014 at 5:50 PM, Jan Slodicka  wrote:
> So one could replace "LIKE 'xxx%'" by "BETWEEN('xxx', 'xxx' + '\uDBFF\uDFFD').

make that

BETWEEN('xxx', 'xxx' + char(1114109))

I don't think SQlite supports \u literals, nor does it support hex
literals, so must use the decimal equivalent to U+10FFFD.

 C:\Users\DDevienne>sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select '\uDBFF\uDFFD';
\uDBFF\uDFFD
sqlite> select char(1114109);
􏿽
sqlite> select char(0x10FFFD);
Error: unrecognized token: "0x10FFFD"
sqlite> select typeof(char(1114109));
text
sqlite> select length(char(1114109));
1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Tue, May 13, 2014 at 6:50 PM, Jan Slodicka  wrote:

> Any comments are welcome.


​This is very interesting Jan. The only way this could fail is if the
collation implementation does something funny if it encounters this
character​, e.g. choose to ignore it when comparing. Since most collations
end up calling the OS unicode API, and this handles U+10FFFD correctly,
this should be a very rare case.

This may be a reason for Dr Hipp to reject adding this to the LIKE
optimization, but anyone could choose to make the optimization manually
using BETWEEN instead of LIKE if they are sure that any custom collations
they have coded and are using handle U+10FFFD correctly. This is obviously
true in your case.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Jan Slodicka]

Constantine Yannakopoulos wrote
> On Mon, May 12, 2014 at 4:46 PM, Jan Slodicka 

> jano@

>  wrote:
> I understand that it is difficult to find the least greater character of a
> given character if you are unaware of the inner workings of a collation,
> but maybe finding a consistent upper limit for all characters in all
> possible collations is not impossible?
> 
> -- Constantine
> ___
> sqlite-users mailing list

> sqlite-users@

> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

I could be wrong when I refused this idea. U+10FFFD (represented as
surrogate pair "\uDBFF\uDFFD") may be that character. So one could replace
"LIKE 'xxx%'" by "BETWEEN('xxx', 'xxx' + '\uDBFF\uDFFD').

U+10FFFD is the highest codepoint from the Unicode Private Use Area (PUA).
Unicode standards do not specify anything for this area. So vendors and/or
apps (MS Word for example) can define here their own characters incl. their
properties (font, sorting etc.). I saw somewhere that somebody placed here
Japanese Kanji characters, for example.

msdn statement for CompareString: "All characters in the PUA are sorted
after all other Unicode characters. Within the area, characters are sorted
in numerical order." Hence Windows is OK.

I could not find any such notion for UCI library, but when I tested their
interactive demo, character U+10FFFD was really sorted at the end. (Even
with Japanese, Hindi etc.) That might mean that at least the standard setup
of the UCI library sorts U+10FFFD in the expected way. Of course, this is no
proof - UCI library can be customized.

After all, it seems that U+10FFFD is a good choice, at least for a vast
majority of cases.

BTW, my tests showed that the speed improvement of the LIKE optimization is
in the range 10x-100x.

Any comments are welcome.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75643.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Jan Slodicka]

> ​It can be implemented if the definition of a collation is extended to be 
able to provide this information as Simon suggested.

Sure, this could be done. But I am not sure whether it would be that
usefull. For example I would not use it for my current application (C#).



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75638.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Mon, May 12, 2014 at 6:45 PM, Simon Slavin  wrote:

> They're not the same, Jan.  This optimization is for one very specific use
> of LIKE: where the match string has no wildcards except for a percent sign
> at the end


In theory a LIKE can be optimized if it has a fixed part at the beginning​
and any combination of wildcards after that. The fixed part is used to
define a range in an index (if there is one) and then the wildcard part -if
not a plain '%' taht defines 'everything'- is applied as a filter to each
record in the range to narrow down the result. Now, whether this is to be
preferred instead of a full table scan should be a job for the query
planner.

--Constantine
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Simon Slavin]

On 12 May 2014, at 4:05pm, Jan Slodicka wrote:

> And whether we'll call it "LIKE optimization" or "using BETWEEN", the
> problem remains basically the same.

They're not the same, Jan.  This optimization is for one very specific use of 
LIKE: where the match string has no wildcards except for a percent sign at the 
end.  I don't think it's out of order to say "Wildcard matching is wildcard 
matching, which is slow.  If you want fast, use BETWEEN.".

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Mon, May 12, 2014 at 5:41 PM, Jan Slodicka  wrote:

> I think that the answer is as before: The LIKE optimization cannot be
> performed by the DB engine, but only by the collation author.
>

​It can be implemented if the definition of a collation is extended to be
able to provide this information as Simon suggested. For example, by
defining a second callback that provides the lower and upper bounds that
enclose the results of a LIKE prefix string. If this callback is not
implemented or if it returns null strings then no optimization should be
attempted.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Mon, May 12, 2014 at 5:22 PM, Simon Slavin  wrote:

> agree: it's not possible to deduce an optimization without understanding
> the collation.  It might be possible to introduce it in a future version of
> SQLite by requiring anyone who writes an optimization to supply routines
> which work out the proper strings for comparison.
>

​Indeed. And if these routines are not implemented (e.g. for Japanese that
do not have ordering)​ then no LIKE optimization should be attempted by the
engine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Jan Slodicka]

> However, I don't think it's necessary to solve this problem.  Just don't
try to optimize it.  Whoever is doing the programming knows that pattern
matching is slow.  If they want a fast solution they'll use BETWEEN instead. 
And that will make them have to provide their own comparison strings.
 
It looks like you are now talking to Dr. Hipp.

Because we - application programmers - will eventually have to do the
optimization; the users will tell us when they are frustrated from the
waiting.

And whether we'll call it "LIKE optimization" or "using BETWEEN", the
problem remains basically the same.




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75624.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Jan Slodicka]

> ​I understand that it is difficult to find the least greater character of a 
given character

If you understand this, then you must admit that it cannot be done by the DB
engine.

Secondly:

I pointed out to the problems when deriving the string "abd" from "abc".
However, you suggest now a different way: extending "abc" to "abcX". Here
are first problems that came to my mind:

- Some languages do not have any official ordering at all - take Japanese,
for example.

- CZE/SVK: Adding 'Z' may lead to "DZ". (specific sorting applies)

- Look at Unicode code charts. What would you select? Many (if not most)
characters with high codes would be probably ignored by the collation. In
such a case your optimization would lead to no results at all.

I think that the answer is as before: The LIKE optimization cannot be
performed by the DB engine, but only by the collation author.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75623.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Simon Slavin]

On 12 May 2014, at 3:05pm, Constantine Yannakopoulos wrote:

> ​I understand that it is difficult to find the least greater character of a
> given character if you are unaware of the inner workings of a collation,
> but maybe finding a consistent upper limit for all characters in all
> possible collations is not impossible?

I agree: it's not possible to deduce an optimization without understanding the 
collation.  It might be possible to introduce it in a future version of SQLite 
by requiring anyone who writes an optimization to supply routines which work 
out the proper strings for comparison.

However, I don't think it's necessary to solve this problem.  Just don't try to 
optimize it.  Whoever is doing the programming knows that pattern matching is 
slow.  If they want a fast solution they'll use BETWEEN instead.  And that will 
make them have to provide their own comparison strings.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Stephan Beal]

On Mon, May 12, 2014 at 4:05 PM, Constantine Yannakopoulos <
alfasud...@gmail.com> wrote:

> ​I understand that it is difficult to find the least greater character of a
> given character if you are unaware of the inner workings of a collation,
> but maybe finding a consistent upper limit for all characters in all
> possible collations is not impossible?
>


i don't know if this helps, but i recently ran across a query both in the
Fossil SCM and in SVN (via a post on this list) which does something
similar for paths:

char const * zCollation = fsl_cx_filename_collation(f);
rc = fsl_db_prepare(db, ,
"SELECT id FROM vfile WHERE vid=%"FSL_ID_T_PFMT
" AND (pathname=%Q %s "
"OR (pathname>'%q/' %s AND pathname<'%q0' %s))",
(fsl_id_t)vid,
zName, zCollation, zName,
zCollation, zName, zCollation )

that zCollation part resolves to either an empty string or "COLLATE
nocase," depending on application-level settings. zPath is a filename or
dir name. the 'vid' part there is not relevant for you, but the pathname
conditions sound similar to what you are trying to achieve.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Jan Slodicka]

Hi Clemens,

I know that link very well. I answered Constantine's statement who claimed
that Sqlite implementation could be less restrictive. I just tried to
explain why it is not possible.

Jan



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75621.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Mon, May 12, 2014 at 4:46 PM, Jan Slodicka  wrote:

> Sqlite LIKE optimization is described as follows:
> A like pattern of the form "x LIKE 'abc%'" is changed into constraints
> "x>='abc' AND x<'abd' AND x LIKE 'abc%'"
>

Actually, I would do something ​like:

"x>='abc' AND x<'ab
​c​
'
​ || ​
AND x LIKE 'abc%'"

​where  is a string with a single character that is
guaranteed to be greater than -and NOT equal to- any other character. For
instance, if the encoding was single-byte ANSI​ it would be something like:

"x>='abc' AND x<'ab
​c​
'
​ || Char(255)
AND x LIKE 'abc%'"

​I understand that it is difficult to find the least greater character of a
given character if you are unaware of the inner workings of a collation,
but maybe finding a consistent upper limit for all characters in all
possible collations is not impossible?

-- Constantine
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Clemens Ladisch]

Jan Slodicka wrote:
> Sqlite LIKE optimization is described as follows:
> A like pattern of the form "x LIKE 'abc%'" is changed into constraints
> "x>='abc' AND x<'abd' AND x LIKE 'abc%'"
>
> If you look into sqlite code, then the string "abd" is generated from "abc"
> using a trivial algebra.
>
> However, this algebra won't work on a custom collation.

 says:
| Terms that are composed of the LIKE or GLOB operator can sometimes be
| used to constrain indices. There are many conditions on this use:
| [...]
| 6. For the LIKE operator, if case_sensitive_like mode is enabled then
|the column must indexed using BINARY collating sequence, or if
|case_sensitive_like mode is disabled then the column must indexed
|using built-in NOCASE collating sequence.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Jan Slodicka]

Sqlite LIKE optimization is described as follows:
A like pattern of the form "x LIKE 'abc%'" is changed into constraints
"x>='abc' AND x<'abd' AND x LIKE 'abc%'"

If you look into sqlite code, then the string "abd" is generated from "abc"
using a trivial algebra.

However, this algebra won't work on a custom collation. Right now I am
investigating a similar issue (we use custom case-insensitive collations)
and come to the conclusion that the LIKE optimization cannot be done without
the specific knowledge of the collation used.

In other words, it cannot be done by the Sqlite engine.

Two extreme examples:

CZE/SVK: 
If you replace "x LIKE 'ch%'" by "ch < x AND x < 'ci'", you'll get no
results.

DAN/NOR:
How would you replace "aa%"? (Note this correct sorting: Zorro < Aaron)
Or what about this: "\u0061\u030A"? (Å written with combining diacritics.)

Look  here    for more
problems.





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75617.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Sun, Feb 9, 2014 at 8:27 PM, Simon Slavin  wrote:

> I know it's a hack.  But it's an elegant efficient hack that takes
> advantage of the things SQLite does well.  As long as that's the only way
> you were using LIKE.
>

Don't get me wrong, the solution is good. But apart from the specific
problem I also started the thread in order to prove that the implementation
of the LIKE optimization in SqLite is not all it could be. Apart from mixed
languages there are other, less extreme scenarios where a
collation-sensitive like optimization will come in handy. For instance, for
languages with accents it would be nice to be able to create a
case-insensitive accent-insensitive (CI_AI) collation and be able to use
LIKE on it, even if it doesn't use an index. And overloading the LIKE
operator globally is not a good idea because it will affect all LIKE
operations in a database, even in columns that are not CI_AI.

I was hoping to elicit a response from D. R. Hipp but he has chosen not to
respond.

--
Constantine
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Simon Slavin]

On 9 Feb 2014, at 6:05pm, Constantine Yannakopoulos  
wrote:

> - You are assuming that 'z' is the higher order character that can appear
> in a value. This is not the case; for instance greek characters have higher
> order than 'z'. This can be fixed (only for latin/greek) by using the
> highest order greek character 'ώ' (accented omega) instead of 'z'; but I
> would prefer a very high-order non-printable one instead.

By all means replace the '' I typed with a string of 200 accented 
lower-case omegas.  Or with (char)0x7F or some other equivalent.  Since (if I 
remember my Greek) it's impossible for a word to contain three omegas in a row, 
I doubt you'll come across any occurrences of it from anyone who is using your 
program realistically.

Hmm.  I assume that some internal part of SQLite would actually be putting this 
string through your collation function.  It would presumably happen 
automatically as part of what SQLite does.

I know it's a hack.  But it's an elegant efficient hack that takes advantage of 
the things SQLite does well.  As long as that's the only way you were using 
LIKE.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

Thank you all for your
replies,

@RSmith:

My question is about the _specific_ case:

SELECT * FROM ATable WHERE AColumn LIKE
'FixedSearchPart' || 'SomeWildCard'

where the right side of the LIKE operator is a constant that has a fixed
part at the beginning and a wildcard after that. The optimization is that
the index is used to produce a rowset in which AColumn starts with
'FixedSearchPart' and then rows are filtered according to 'SomeWildCard'.
For instance, in

SELECT * FROM ATable WHERE AColumn LIKE
'Constantine%Yann%'

As I understand it, the index scan will use the string 'Constantine' as
argument and then the full string 'Constantine%Yann%' will be used to
further filter the rowset. Of course any other case that has no fixed part
at the start of the right-side string will have to fall back to a full scan
and filter.

Maybe the parameter notation was a little confusing but from the
description you can deduce that it will not contain a wildcard.
Essentially, the user will be asking for the rows where AColumn BEGINS WITH
a string.

@Jean-Christophe:

Thank you for the extension. I will certainly have a look at it, but I
already have the comparison algorithm (specific to latin/greek) from
another case. So the only thing I have to do is to tailor it inside a
custom collation.

@Yuriy:

Yes, the current interface for custom functions does not provide any
information on what the arguments are, so it is impossible to deduce the
collation of arguments inside the function body. That's why this has to be
implemented by the SqLite query optimizer itself. I was thinking about
something like:

- The query optimizer understands that the left-side of the LIKE argument
is a column with an index and a collation, and the right side is a literal
with a fixed left part (after parameter substitution).
- It takes the fixed part, appends a very high-order (preferably
non-printable) character to it and use the index to materialize this clause:

  AColumn BETWEEN 'FixedPart' AND 'FixedPart' || .

If we were using ANSI I would suggest something like:

  AColumn BETWEEN 'FixedPart' AND 'FixedPart' || (char)0xFF

but for UTF-8 I am not sure what to suggest. It would have to be a UTF8
character value that is guaranteed to be greater than any other usable
character in any code page. I am not sure whether there actually is one.

-Then it applies the whole right-side argument to this rowset using LIKE
pattern matching to produce the correct result.

@Simon:

Your proposal is very clever but it has two weaknesses:
- You are assuming that 'z' is the higher order character that can appear
in a value. This is not the case; for instance greek characters have higher
order than 'z'. This can be fixed (only for latin/greek) by using the
highest order greek character 'ώ' (accented omega) instead of 'z'; but I
would prefer a very high-order non-printable one instead.
- It assumes that the column has a maximum width. Since SQLite does not
have maximum widths for columns of TEXT affinity (although a max width can
be implied and imposed by the app itself), improbable as it may be, there
could be a row with the value :SearchString||''||'a' which will
erroneously not make it into the result set. Again, the very high-order
non-printable character would solve this.
So it can be a solution for a specific case with a given max value and a
known set of code pages but it cannot be a general solution.

--Constantine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Simon Slavin]

On 9 Feb 2014, at 4:21pm, Yuriy Kaminskiy  wrote:

> Unfortunately, builtin LIKE ignores collation, and if you override LIKE (as in
> ICU extension), sqlite won't use index for optimization.

However, this particular use of LIKE ...

> SELECT * FROM ATable WHERE AColumn LIKE :SearchString || '%'

is really just a call to BETWEEN:

SELECT * FROM ATable WHERE AColumn BETWEEN :SearchString AND 
:SearchString||''

And BETWEEN translates into >= and <=.  And those /will/ use the index.

So implementing a 'greek soundex' function as a collation would be useful, if 
this is the only use you need to make of LIKE.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Yuriy Kaminskiy]

Constantine Yannakopoulos wrote:
> I have a case where the user needs to perform a search in a text column of
> a table with many rows. Typically the user enters the first n matching
> characters as a search string and the application issues a SELECT statement
> that uses the LIKE operator with the search string:
> 
> SELECT * FROM ATable WHERE AColumn LIKE :SearchString || '%'
> 
> According to the LIKE optimization this statement will use an index so it
> will be fast.

FWIW, sqlite only use index if pattern is string constant or placeholder, it
won't try to use index optimization with more complex constant expression.
I.e.
   SELECT * FROM ATable WHERE AColumn LIKE :SearchString
and append % to your search string outside of sql.
(Well, it does not matter, as I think your following question does not have
acceptable solution).

> The application is used by Greek users. The greek alphabet has some letters
> that are visually identical to corresponding latin letters when in
> capitals, (e.g. A, E, I, O, B, H, K, etc), some that are different but
> match deterministically to a single latin letter (e.g. Σ = S, Φ = F, Ω = O
> etc.) and some that don't have a deterministic match (e.g. Γ = Gh or Yi).

You may want to look at ICU extension, but then sqlite won't use index.

Another option may be fts extension (likely, with custom tokenizer function).

> The table contains strings that consist of words that can be written in
> either latin or greek characters; sometimes even mixed (the user changed
> input locale midword before typing the first non-common letter). I have a
> request that the search should match strings that are written with either
> latin or greek or mixed letters, e.g. "MIS" should match "MISKO" (all
> latin), "ΜΙΣΚΟ" (all greek) or "MIΣΚΟ" (first two letters latin, rest
> greek). I thought of using a custom collation that does this type of
> comparison, have the column use that collation and create an index on that
> column to speed up the search but I discovered that the LIKE operator
> either will not use collations other than BINARY and NOCASE (pragma
> case_sensitive_like) or (if overloaded to perform custom matching) will not

I think "will not use index" is a bug that was fixed in latest sqlite version;
however, "ignoring collation" is intended behavior, and not easy to change :-(.

> use an index, and, worse yet, its behaviour will be the same to all string
> comparisons regardless of collation. So, a full table scan seems inevitable.
> I was wondering whether it is realistic to ask for the LIKE operator to use
> by default the assigned collation of a column. I assume that an index on

From first look, it won't be easy. Probably impossible without changing current
sqlite interface for user-defined collation/function/etc. Besides, it will break
compatibility with existing code, so would require some new PRAGMA to enable.

> that column is using by default the specified collation of the column for
> comparisons, so a LIKE clause like the aforementioned can use the index and
> perform a fast search while using the "mixed" comparison I need. This would
> transparently solve my problem and make the case_sensitive_like pragma
> redundant, but for backward compatibility this behaviour could be activated
> by a new pragma.
> 
> Are there any details I am missing that prevent this from being implemented?
> 
> Thanks in advance.
> 
> --Constantine.


RSmith wrote:
[...]
> It will be a matter of finding the most acceptable deficit... Whether it
> be size, time waste, upgrade cost etc.  By the way, I don't think
> upgrading the table schemata need to be a real hard thing... some
> scripts can take care of that in minimum amount of time. (Test them
> thoroughly though). Also, another poster here had developed a full set
> of international collations and comparison mechanisms as a loadable
> extension to SQLite - Nunicode by Aleksey Tulinov I think... link here:
>
> https://bitbucket.org/alekseyt/nunicode

Unfortunately, builtin LIKE ignores collation, and if you override LIKE (as in
ICU extension), sqlite won't use index for optimization.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[RSmith]

On 2014/02/09 13:18, Constantine Yannakopoulos wrote:

Hello all,

I have a case where the user needs to perform a search in a text column of
a table with many rows. Typically the user enters the first n matching
characters as a search string and the application issues a SELECT statement
that uses the LIKE operator with the search string:

SELECT * FROM ATable WHERE AColumn LIKE :SearchString || '%'

According to the LIKE optimization this statement will use an index so it
will be fast.


This can't be true, there is no way a LIKE or GLOB operator can always use an Index (I mean, it can "use" the Indexed column, but it 
cannot always reduce the number of iterations via Indexing).


To explain more what I mean, consider a simple binary search, let's say we have an ordered list of names that we use the alphabet as 
an indexer:


0:Abraham
1:Ben
2:Constantine
3:Igor
4:James
5:John
6:Ryan
7:Simon

A binary search would start by hitting the middle item typically ( i = Length div 2 ) which is 4 in this case, then comparing it to 
the searched item, let's say it is "JOH" in this case.  It sees that Idx 4 is James, which is smaller than "JOH" (no-case collation 
enabled), then looks to divide the remainder of items larger than James (Idx 5, 6 and 7) in two (3 div 2 = 1 ) and adds it to the 
bottom of them (5) so it checks Index 6, which is now Ryan and is higher than "JOH", it then divides into below Ryan and above James 
and obviously gets "John" which is a match.


A binary tree works similar with the difference it does not have to divide anything, the tree node children are already divisive so 
it just follows down the node closest to the target match until a definite match or matches is/are found (depending on search criteria).


The list above does however demonstrate why a LIKE operator cannot always use an Index, let's say I'm using a search for LIKE '%n', 
how on Earth would you be able to look for that by binary jumping through the list? ANY Indexed item might end on an n, indeed 4 of 
those above do, there is no way to tell and a full-table scan is inevitable.


Of course some clever DB systems, of which SQLite is one, can detect when you use LIKE "Jam%" and knows this is index-searchable and 
still use the Index, but it all depends on what you type and where those % signs are - something which is again negated if the 
search collation does not match the column collation, but is rather easy when standard text or binary collations are used.




store two text columns in the table.  The first is the text as entered.
  The second is your text reduced to its simplified searchable form,
probably all LATIN characters, perhaps using some sort of soundex.  Search
on the second column but return the text in the first.

This allows you to write your conversion routine in the language you're
using to do the rest of your programming, instead of having to express it
as a SQLite function.


Thanks for your reply,


Yes. I thought of that as well. I even have the greek soundex() function
from a previous implementation. Problem is it will bloat the database
considerably, keeping in mind that the users will typically demand that 
ALL
searchable text columns in the application work that way, and who can 
blame
them? And the project manager will not be very keen on accepting both 
this
database size increase and the time needed to calculate the extra 
soundex
column for every database row. It will be much easier to convince this
person to accept the time-costly database upgrade needed in both cases
(tables need to be recreated to change collation) but not both upgrade 
and
bloat.


And I am not happy to accept the fact that I cannot fly, but the laws of the 
Universe demands I adhere to the deficit, and when I simply have to fly, employ 
the help of a very large costly winged tube with jet engines attached to it!

It will be a matter of finding the most acceptable deficit... Whether it be 
size, time waste, upgrade cost etc.  By the way, I don't think upgrading the 
table schemata need to be a real hard thing... some scripts can take care of 
that in minimum amount of time. (Test them thoroughly though). Also, another 
poster here had developed a full set of international collations and comparison 
mechanisms as a loadable extension to SQLite - Nunicode by Aleksey Tulinov I 
think... link here:

https://bitbucket.org/alekseyt/nunicode





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Sun, Feb 9, 2014 at 1:25 PM, Simon Slavin  wrote:

> store two text columns in the table.  The first is the text as entered.
>  The second is your text reduced to its simplified searchable form,
> probably all LATIN characters, perhaps using some sort of soundex.  Search
> on the second column but return the text in the first.
>
> This allows you to write your conversion routine in the language you're
> using to do the rest of your programming, instead of having to express it
> as a SQLite function.
>

Thanks for your reply,


Yes. I thought of that as well. I even have the greek soundex() function
from a previous implementation. Problem is it will bloat the database
considerably, keeping in mind that the users will typically demand that ALL
searchable text columns in the application work that way, and who can blame
them? And the project manager will not be very keen on accepting both this
database size increase and the time needed to calculate the extra soundex
column for every database row. It will be much easier to convince this
person to accept the time-costly database upgrade needed in both cases
(tables need to be recreated to change collation) but not both upgrade and
bloat.

--
Constantine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Simon Slavin]

On 9 Feb 2014, at 11:18am, Constantine Yannakopoulos  
wrote:

> So, a full table scan seems inevitable.

I can't answer the specific question you asked, but I have a suggestion for 
your program: store two text columns in the table.  The first is the text as 
entered.  The second is your text reduced to its simplified searchable form, 
probably all LATIN characters, perhaps using some sort of soundex.  Search on 
the second column but return the text in the first.

This allows you to write your conversion routine in the language you're using 
to do the rest of your programming, instead of having to express it as a SQLite 
function.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE and GLOB questions

[Richard Hipp]

On Tue, Apr 30, 2013 at 12:52 PM, Staffan Tylen wrote:

> I have the following two questions to share:
>
> First, assume two tables t1 and t2 where t1 has a text column a with data
> and t2 has a text column p with patterns in LIKE format. For each a in t1 I
> want to find all matching patterns p in t2. Is this possible using a single
> SELECT clause? I've been unable to come up with the expression on the
> right-hand side of LIKE to make this work.
>

SELECT p FROM t1, t2 WHERE t1.a LIKE t2.p;


>
> Second, not having much *NIX knowledge, what's the difference between LIKE
> and GLOB apart from the masking characters and case-sensitivity? The
> documentation doesn't provide much information and Wikipedia shows that
> there are many 'standards' out there. How does GLOB work in SQLite? One or
> two examples of how LIKE and GLOB operate differently would be very
> helpful.
>

LIKE is case insensitive and uses wildcards '%' and '_'

GLOB is case sensitive and uses wildcards '*' and '?'.

GLOB also allows you to say '[abcd]' to mean any character in the set of
"abcd".

LIKE can have an option ESCAPE character for escaping wildcards.  GLOB
cannot.  But with GLOB if you want to match a wildcard character you can
use '[*]' or '[?]'.

Other than that they are the same.  In fact, GLOB and LIKE are implemented
using same subroutine, called with different parameters that determine the
wildcards and case sensitivity.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE and GLOB questions

[Clemens Ladisch]

Staffan Tylen wrote:
> First, assume two tables t1 and t2 where t1 has a text column a with data
> and t2 has a text column p with patterns in LIKE format. For each a in t1 I
> want to find all matching patterns p in t2. Is this possible using a single
> SELECT clause?

SELECT * FROM t1 JOIN t2 ON t1.a LIKE t2.p

> Second, not having much *NIX knowledge, what's the difference between LIKE
> and GLOB apart from the masking characters and case-sensitivity?

Character classes.

A comment hidden in the source code explains:

** Globbing rules:
**
**  '*'   Matches any sequence of zero or more characters.
**
**  '?'   Matches exactly one character.
**
** [...]  Matches one character from the enclosed list of
**characters.
**
** [^...] Matches one character not in the enclosed list.
**
** With the [...] and [^...] matching, a ']' character can be included
** in the list by making it the first character after '[' or '^'.  A
** range of characters can be specified using '-'.  Example:
** "[a-z]" matches any single lower-case letter.  To match a '-', make
** it the last character in the list.
**
** This routine is usually quick, but can be N**2 in the worst case.
**
** Hints: to match '*' or '?', put them in "[]".  Like this:
**
** abc[*]xyzMatches "abc*xyz" only


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Dominique Devienne]

On Wed, Feb 27, 2013 at 3:16 PM, Igor Tandetnik  wrote:

> On 2/27/2013 4:35 AM, Dominique Devienne wrote:
>
>> PS: Something else that should also be part of SQLite built-in is the
>> optimization that col LIKE 'prefix%' queries should implicitly try to use
>> an index on col.
>>
>
> http://www.sqlite.org/**optoverview.html#like_opt


Thanks for the reminder. Note though that last time I checked [1], this
didn't work for a multi-column index, even if the column involved in a
prefix-based like-where-clause is first in the index. --DD

[1]
http://stackoverflow.com/questions/11152371/how-to-improve-the-performance-of-query-with-where-and-group-by-in-sqlite/11736532#11736532
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Igor Tandetnik]

On 2/27/2013 4:35 AM, Dominique Devienne wrote:

PS: Something else that should also be part of SQLite built-in is the
optimization that col LIKE 'prefix%' queries should implicitly try to use
an index on col.


http://www.sqlite.org/optoverview.html#like_opt

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Dominique Devienne]

On Wed, Feb 27, 2013 at 11:23 AM, Clemens Ladisch wrote:

> Dominique Devienne wrote:
> > My $0.02 is that such a chr() function could/should be built-in to
> SQLite.
>
> Apparently, drh has a time machine:
> http://www.sqlite.org/cgi/src/info/209b21085b
>

Indeed! Spooky :) --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Clemens Ladisch]

Dominique Devienne wrote:
> My $0.02 is that such a chr() function could/should be built-in to SQLite.

Apparently, drh has a time machine:
http://www.sqlite.org/cgi/src/info/209b21085b


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Dominique Devienne]

On Tue, Feb 26, 2013 at 2:31 PM, Clemens Ladisch  wrote:

>   ... 'somedata/' || CAST(x'F48FBFBF' AS TEXT)
>

Great trick! But it hardly qualifies as user friendly though, no?

For our app, I added a chr() SQL function that take an arbitrary number of
integers and UTF-8 encodes them:

register_function(-1, "chr", codepoint_to_utf8);

so the above becomes

... 'somedata/' || chr(1114111)

Of course, the fact that it's a decimal code-point number is not ideal
since less expressive than

... 'somedata/' || chr(0x10)

but hexa-literals are not supported in SQL it seems (I tried just SQLite
and Oracle).

My $0.02 is that such a chr() function could/should be built-in to SQLite.
--DD

PS: Something else that should also be part of SQLite built-in is the
optimization that col LIKE 'prefix%' queries should implicitly try to use
an index on col. I suspect it may be more difficult than I expect because
of collation, but absent custom collations, I wish that optimization was
available.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Jay A. Kreibich]

On Tue, Feb 26, 2013 at 12:34:03PM +, Simon Slavin scratched on the wall:
> On 26 Feb 2013, at 7:39am, dd  wrote:

> >   This database has unicode strings(chinese/japanese/...etc strings). can
> > you tell me which is the correct character to replace with z?
> 
> Ah.  There you have a problem because internally SQLite does not
> handle language support within Unicode characters.  I'm going to let
> someone with SQLite/Unicode expertise answer this one, but it may be
> that with Unicode even your LIKE command would not have worked
> properly and you should use something like

  The only issue there is that the default case-insensitive nature of
  LIKE won't work.  Otherwise LIKE should have no problems with
  matching unicode strings.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Igor Tandetnik]

On 2/26/2013 9:25 AM, dd wrote:

Igor/Clemen Ladisch,


SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/z"


I want to replace z with 10 character. But, it's failed.


Failed in what way? How do you run your query? Show your code.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Igor Tandetnik]

On 2/26/2013 9:18 AM, dd wrote:

10 decimal value is 1114111. But, some chinese characters are greater
than this value.


You are mistaken. There are no Unicode characters above U+10, 
whether Chinese or otherwise.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[dd]

Igor/Clemen Ladisch,

>>SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/z"

I want to replace z with 10 character. But, it's failed. what is the
correct decimal value for that?


On Tue, Feb 26, 2013 at 6:18 PM, dd  wrote:

> 10 decimal value is 1114111. But, some chinese characters are greater
> than this value. Is it correct character(10) to replace with z?
>
> Please correct me if I am doing wrong.
>
>
> On Tue, Feb 26, 2013 at 5:58 PM, Igor Tandetnik wrote:
>
>> On 2/26/2013 8:31 AM, Clemens Ladisch wrote:
>>
>>> Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote:
>>>
 SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
> "somedata/zzz"
>
> This database has unicode strings(chinese/japanese/...**etc strings).
> can
> you tell me which is the correct character to replace with z?
>

 U+, of course.

>>>
>>> Unicode characters can have more than 16 bits, of course.
>>>
>>
>> ... but SQLite orders them with simple memcmp (absent a custom
>> collation), so 0x will still compare greater than any surrogate pair.
>>
>> If the database file uses UTF-8 encoding, and contains supplemental
>> characters, then yes, a UTF-8 representation of U+10 would be prudent.
>> --
>> Igor Tandetnik
>>
>>
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[dd]

10 decimal value is 1114111. But, some chinese characters are greater
than this value. Is it correct character(10) to replace with z?

Please correct me if I am doing wrong.


On Tue, Feb 26, 2013 at 5:58 PM, Igor Tandetnik  wrote:

> On 2/26/2013 8:31 AM, Clemens Ladisch wrote:
>
>> Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote:
>>
>>> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
 "somedata/zzz"

 This database has unicode strings(chinese/japanese/...**etc strings).
 can
 you tell me which is the correct character to replace with z?

>>>
>>> U+, of course.
>>>
>>
>> Unicode characters can have more than 16 bits, of course.
>>
>
> ... but SQLite orders them with simple memcmp (absent a custom collation),
> so 0x will still compare greater than any surrogate pair.
>
> If the database file uses UTF-8 encoding, and contains supplemental
> characters, then yes, a UTF-8 representation of U+10 would be prudent.
> --
> Igor Tandetnik
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Igor Tandetnik]

On 2/26/2013 8:31 AM, Clemens Ladisch wrote:

Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote:

SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz"

This database has unicode strings(chinese/japanese/...etc strings). can
you tell me which is the correct character to replace with z?


U+, of course.


Unicode characters can have more than 16 bits, of course.


... but SQLite orders them with simple memcmp (absent a custom 
collation), so 0x will still compare greater than any surrogate pair.


If the database file uses UTF-8 encoding, and contains supplemental 
characters, then yes, a UTF-8 representation of U+10 would be prudent.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Clemens Ladisch]

Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote:
>> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz"
>>
>> This database has unicode strings(chinese/japanese/...etc strings). can
>> you tell me which is the correct character to replace with z?
>
> U+, of course.

Unicode characters can have more than 16 bits, of course.

RFC 3629 restricts UTF-8-encoded characters to U+10.
In SQL, that would be:

  ... 'somedata/' || CAST(x'F48FBFBF' AS TEXT)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Igor Tandetnik]

On 2/26/2013 2:39 AM, dd wrote:

>>SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
"somedata/zzz"

This database has unicode strings(chinese/japanese/...etc strings). can
you tell me which is the correct character to replace with z?


U+, of course.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Simon Slavin]

On 26 Feb 2013, at 7:39am, dd  wrote:

>>> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
>>> "somedata/zzz"
> 
>   This database has unicode strings(chinese/japanese/...etc strings). can
> you tell me which is the correct character to replace with z?

Ah.  There you have a problem because internally SQLite does not handle 
language support within Unicode characters.  I'm going to let someone with 
SQLite/Unicode expertise answer this one, but it may be that with Unicode even 
your LIKE command would not have worked properly and you should use something 
like

SELECT * FROM emp WHERE substr(column_test,1,9) = 'somedata/'

Which will slower because it will not be able to use an index for optimization. 
 But it might be faster than the LIKE because using a regexp for matching seems 
likely to me to be slower than substr().

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[dd]

   >>SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
"somedata/zzz"

   This database has unicode strings(chinese/japanese/...etc strings). can
you tell me which is the correct character to replace with z?




On Mon, Feb 25, 2013 at 8:13 PM, Simon Slavin  wrote:

>
> On 25 Feb 2013, at 2:46pm, dd  wrote:
>
> >  Table has string data type column. format of strings:
> > somedata1/somedata2/somedata3
> >
> >  I have written query to search : select * from emp where column_test
> like
> > "somedata/%";
> >
> >  It gives perfomance as per articles in internet. Is it? If yes, what is
> > alternate query for this?
>
> If the format of your 'LIKE' clause is always that you have fixed text at
> the beginning, then you can speed up your search a lot.  Create an index on
> the 'column_test' field, and use this query:
>
> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz"
>
> replace 'zzz' with '~~~' or something similar if you're being really fussy.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[dd]

Thanks Richard.


On Mon, Feb 25, 2013 at 6:54 PM, Richard Hipp  wrote:

> On Mon, Feb 25, 2013 at 9:46 AM, dd  wrote:
>
> > Hi,
> >
> >   Table has string data type column. format of strings:
> > somedata1/somedata2/somedata3
> >
> >   I have written query to search : select * from emp where column_test
> like
> > "somedata/%";
> >
> >   It gives perfomance as per articles in internet. Is it? If yes, what is
> > alternate query for this?
> >
>
> The query might go faster if you do:
>
> CREATE INDEX emp_idx1 ON emp(column_test COLLATE nocase);
>
> Or, if you really intended to do a case-sensitive search, you could say:
>
> SELECT * FROM emp WHERE column_test GLOB 'somedata/*';
>
>
>
>
>
> >
> >   Thanks in advance.
> >
> > Best Regards,
> > dd.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Simon Slavin]

On 25 Feb 2013, at 2:46pm, dd  wrote:

>  Table has string data type column. format of strings:
> somedata1/somedata2/somedata3
> 
>  I have written query to search : select * from emp where column_test like
> "somedata/%";
> 
>  It gives perfomance as per articles in internet. Is it? If yes, what is
> alternate query for this?

If the format of your 'LIKE' clause is always that you have fixed text at the 
beginning, then you can speed up your search a lot.  Create an index on the 
'column_test' field, and use this query:

SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz"

replace 'zzz' with '~~~' or something similar if you're being really fussy.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Richard Hipp]

On Mon, Feb 25, 2013 at 9:46 AM, dd  wrote:

> Hi,
>
>   Table has string data type column. format of strings:
> somedata1/somedata2/somedata3
>
>   I have written query to search : select * from emp where column_test like
> "somedata/%";
>
>   It gives perfomance as per articles in internet. Is it? If yes, what is
> alternate query for this?
>

The query might go faster if you do:

CREATE INDEX emp_idx1 ON emp(column_test COLLATE nocase);

Or, if you really intended to do a case-sensitive search, you could say:

SELECT * FROM emp WHERE column_test GLOB 'somedata/*';





>
>   Thanks in advance.
>
> Best Regards,
> dd.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like operator

[Kees Nuyt]

On Thu, 9 Dec 2010 23:23:37 -0800 (PST), salmaan
 wrote:

>Hi
>
>i need your help i want a query...
>
>i have a question for you suppose i have table name person in that column
>name description which contains the below words
>
>The govt has asked airlines to issue an all-inclusive and reasonable fares
>in a consumer-friendly manner, something that only Air India (domestic) has
>done
>
>i want a like(%) query which will give output 20 character from the
>selection suppose if i write a query
>
>SELECT * FROM Person
>WHERE description LIKE '%gov%'
>
>it will give the output
>
>The govt has asked airlines to issue an all-inclusive and reasonable fares
>in a consumer-friendly manner, something that only Air India (domestic) has
>done
>
>what i want  only 20 or 30 chracter from the like(%govt%) it can be any like
>character (%has%) or(%to%) it may vary but the o/p must be 20 or 30
>character from the like(%) it should not contains all the charter
>
>the output must in this way 
>govt has asked airlines to issue an all-inclusive and reasonable fares in a
>consumer-friendly manner

This example does not match your requirement. It's over 100
characters, not 20 to 30. According your requirement, it would be
'govt has asked airlines to is...'

This is best done in the host language, you can get the offset of
the LIKE term with the charindex() or pos() function and cut off the
string with the substr() function. The scope of sqlite is data and
set operations, the scope of the host language is processing and
presentation.

SQLite does offer substr(), but not charindex().
There are sqlite extension libraries which do support extra
functions, for example:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg21791.html

(download the zip file and have a look at source file func_exp.c)
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE with BLOB

[Igor Tandetnik]

Matthew Jones  wrote:
> From a web search (and abbreviated):
> 
>>> I have BLOBs in my schema and the data will often start with bytes of
>>> 0 value.
>>> I'm having a tough time coming up with the proper SQL syntax to
>>> select all the columns that start with 2 0's (or any zeros).
> 
>> SELECT * FROM mytable WHERE myblob LIKE X'0025';
>> SELECT * FROM mytable WHERE quote(myblob) LIKE 'X''00%';

Another approach:

SELECT * FROM mytable WHERE substr(myblob, 1, 1) = X'00';

> Now I have a column of blob data (always 20 bytes) and I would like to
> do a LIKE select on this column where I have the first 10 bytes but they
> can be any value including, of course, the % character. Is this possible
> or does the arbitrary nature of the data make this infeasible?

SELECT * FROM mytable WHERE substr(myblob, 1, 10) = ?;

-- 
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE with BLOB

[Pavel Ivanov]

> If it is possible, how would I define a prepared statement so that I can
> just bind the (10 byte) value into it?

Is it possible to pre-process your 10 bytes and insert e.g. symbol '\'
before any '\', '_' and '%' symbol? After that you can query
SELECT * FROM mytable WHERE myblob LIKE ? ESCAPE '\'


Pavel

On Thu, Jul 8, 2010 at 10:46 AM, Matthew Jones  wrote:
>  From a web search (and abbreviated):
>
>  >>I have BLOBs in my schema and the data will often start with bytes of
>  >>0 value.
>  >>I'm having a tough time coming up with the proper SQL syntax to
>  >> select all the columns that start with 2 0's (or any zeros).
>
>  > SELECT * FROM mytable WHERE myblob LIKE X'0025';
>  > SELECT * FROM mytable WHERE quote(myblob) LIKE 'X''00%';
>
> Now I have a column of blob data (always 20 bytes) and I would like to
> do a LIKE select on this column where I have the first 10 bytes but they
> can be any value including, of course, the % character. Is this possible
> or does the arbitrary nature of the data make this infeasible?
>
> If it is possible, how would I define a prepared statement so that I can
> just bind the (10 byte) value into it?
>
> Thanks
>
> --
> Matthew Jones
> Hewlett-Packard Ltd
> Long Down Avenue
> Stoke Gifford
> Bristol.  BS34 8QZ
> Tel:   +44 (0) 117 312 7490
> Email:    matthew.jo...@hp.com
>
> Hewlett-Packard Limited registered Office: Cain Road, Bracknell, Berks,
> RG12 1HN. Registered No: 690597 England
>
> The contents of this message and any attachments to it are confidential
> and may be legally privileged. If you have received this message in
> error, you should delete it from your system immediately and advise the
> sender.
>
> To any recipient of this message within HP, unless otherwise stated you
> should consider this message and attachments as "HP CONFIDENTIAL".
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like do not use index as previous version

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ge...@iol.it
Sent: Wednesday, October 21, 2009 2:03 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Like do not use index as previous version

Hi all,
it seems that in last versions on sqlite3 LIKE clause stopped to use
indexes; I created a new empty database with SQLIte 3.6.13 and I run
these statements  :

CREATE TABLE TEST
(TEXT_1 text PRIMARY KEY, TEXT_2 text, TEXT_3 text COLLATE NOCASE);
CREATE INDEX TEST_IDX_2 ON TEST (TEXT_2); CREATE INDEX
TEST_IDX_3 ON TEST (TEXT_3);

Running explain query plan on "select * from test where text_3 like
'x';", I have the following result:

0|0|TABLE test WITH
INDEX TEST_IDX_3

And it's what I  expected.

If I execute the some statements 
in SQLite 3.6.16 and 3.6.19, I have this result:

0|0|TABLE test

So It's not 
using the index as in 3.6.13 version. 
The some if I try to use the operator 
GLOB with field text_2: in SQLite 3.6.13 it uses the TEST_IDX_2 index
but it's 
not used in SQLite 3.6.19.

Any suggestions?

==

Regarding suggestions:
   Since "like" and "glob" are intended for use with wildcards and
you're not using wildcards, why not use
 "where text_3 == 'x';"
instead?

 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator to support GREEK

[indiababu]

Hi

Here is the example I worked..



> Creating the table
SqlLite.exe test.db "create table t3 (t3key INTEGER PRIMARY KEY,path
TEXT,num double,timeEnter DATE , validate BOOL);"

> Inserting into the table

SqlLite.exe test.db  "insert into t3 (path,num) values
('D:/CCVweiew/sampleexample/data/path',3);"

SqlLite.exe test.db  "insert into t3 (path,num) values
('D:/CCdfView/sample/dadfta2/abcd',3);"

SqlLite.exe test.db  "insert into t3 (path,num) values
('D:/CCVdfiew/sample/data/efgh',3);"

>
SqlLite.exe test.db  "select * from t3 ";

t3key = 1
path = D:/CCVweiew/sampleexample/data/path
num = 3.0
timeEnter = NULL
validate = NULL

t3key = 2
path = D:/CCdfView/sample/dadfta2/abcd
num = 3.0
timeEnter = NULL
validate = NULL

t3key = 3
path = D:/CCVdfiew/sample/data/efgh
num = 3.0
timeEnter = NULL
validate = NULL

> updating when path matches '/sample/'

SqlLite.exe  test.db "update t3 set validate=500 where path like
'%/sample/%'" 

>SqlLite.exe test.db  "select * from t3 ";


t3key = 1
path = D:/CCVweiew/sampleexample/data/path
num = 3.0
timeEnter = NULL
validate = NULL

t3key = 2
path = D:/CCdfView/sample/dadfta2/abcd
num = 3.0
timeEnter = NULL
validate = 500

t3key = 3
path = D:/CCVdfiew/sample/data/efgh
num = 3.0
timeEnter = NULL
validate = 500

Hope it solves ur probs..

--
ABC


mikewhit wrote:
> 
> Gerasimos Xydas <[EMAIL PROTECTED]> writes:
> 
>> 
>> Hello,
>> 
>> I have built an SQLite 3 database from C code.
>> 
>> CASE 1
>> 
> ...
>> CASE 2
>> 
> ...
>> 
>> Best regards,
>> Gerasimos
>> 
> 
> Search the newsgroup ... start here
> http://thread.gmane.org/gmane.comp.db.sqlite.general/42112
> 
> Regards,
> MikeW
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/LIKE-operator-to-support-GREEK-tp20341577p20374899.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users