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