Re: [sqlite] LIKE Query with ESCAPE character

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

[sqlite] LIKE Query with ESCAPE character

Hi, I hope this is a simple question that someone can answer without a lot of trouble (maybe I am simply misunderstanding something). According to https://www.sqlite.org/optoverview.html#the_like_optimization , a LIKE query can use an ESCAPE character and still be optimized as long as the

Re: [sqlite] like query

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.

Re: [sqlite] like query

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

Re: [sqlite] like query

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

Re: [sqlite] like query

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

Re: [sqlite] like query

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

Re: [sqlite] like query

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.

Re: [sqlite] like query

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

Re: [sqlite] like query

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 ___

Re: [sqlite] like query

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

Re: [sqlite] like query

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

Re: [sqlite] like query

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

Re: [sqlite] like query

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.

Re: [sqlite] like query

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

Re: [sqlite] like query

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.

Re: [sqlite] like query

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

Re: [sqlite] like query

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

Re: [sqlite] like query

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

Re: [sqlite] like query

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

[sqlite] like query

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? Thanks in

Re: [sqlite] SQLite Like Query Optimization

fts3] So how can I use newly created Sqlite3.dll from command prompt to load fts. Thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Monday, February 18, 2008 2:22 PM To: General Discussion of SQLite Database Subject: Re: [s

Re: [sqlite] SQLite Like Query Optimization

"Kalyani Phadke" <[EMAIL PROTECTED]> wrote: > I have not recompiled sqlite 3 before. I am having trouble to find > documentation. > > Could anyone pls tell me how can I compile SQLite3 source code on > windows xp machine. Do I need to download FTS3 files ? Where can I find > those files? How can

Re: [sqlite] SQLite Like Query Optimization

Thanks > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor > Sent: Monday, February 18, 2008 11:10 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite Like Query Optimization > > On 2/18/08

Re: [sqlite] SQLite Like Query Optimization

-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor Sent: Monday, February 18, 2008 11:10 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite Like Query Optimization On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote: > I

Re: [sqlite] SQLite Like Query Optimization

; From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor > Sent: Monday, February 18, 2008 9:50 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite Like Query Optimization > > On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:

Re: [sqlite] SQLite Like Query Optimization

TECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet > Sent: Friday, February 15, 2008 4:38 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite Like Query Optimization > > Hi Kalyani, > > > select ID from TableA where column2 like '%test%' or

Re: [sqlite] SQLite Like Query Optimization

.. -Thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet Sent: Friday, February 15, 2008 4:38 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite Like Query Optimization Hi Kalyani, > select ID from TableA where column2 l

Re: [sqlite] SQLite Like Query Optimization

"Evans, Mark (Tandem)" <[EMAIL PROTECTED]> wrote: > snip... > > > > > LIKE operators cannot use indices unless the index is case > > insensitive. Use GLOB for case sensitive fields. > > > > Richard - i'm not sure i understand "unless the index is case insensitive." > How does that relate

Re: [sqlite] SQLite Like Query Optimization

...snip... > > LIKE operators cannot use indices unless the index is case > insensitive. Use GLOB for case sensitive fields. > Richard - i'm not sure i understand "unless the index is case insensitive." How does that relate to: sqlite> create table t (a varchar(10) primary key, b, c); sqlite>

Re: [sqlite] SQLite Like Query Optimization

Hi Kalyani, > select ID from TableA where column2 like '%test%' or column4like > '%test%' or column5 like '%test%' or column6 like '%test%' or column7 > like '%test%' or column8 like '%test%' order by column3 desc; As already stated, the like operator can't use indexes if you use "or", or

Re: [sqlite] SQLite Like Query Optimization

"Kalyani Phadke" <[EMAIL PROTECTED]> wrote: > I am using Sqlite 3 as my database. One of my table contains 1280010 > rows. Db file size is 562,478KB. I am running DB on Windows XP pro-P4 > CPU 3.20GHz 3.19Hz ,2.00GB of RAM ) > > CREATE TABLE TableA > ( > ID INTEGER NOT NULL PRIMARY KEY

Re: [sqlite] SQLite Like Query Optimization

I'm not 100% sure but Try splitting your query out into 6 different selects, I think the OR clause is the problem... select ID from TableA where column2 like '%test%' union select ID from TableA where column4 like '%test%' HTH Kalyani Phadke <[EMAIL PROTECTED]> wrote: I am using

[sqlite] SQLite Like Query Optimization

I am using Sqlite 3 as my database. One of my table contains 1280010 rows. Db file size is 562,478KB. I am running DB on Windows XP pro-P4 CPU 3.20GHz 3.19Hz ,2.00GB of RAM ) CREATE TABLE TableA ( ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, column1 VARCHAR (50) NOT NULL,

[sqlite] Special characters handling in SQLite Like Query

If I have data as follows, they wont show up when I try to search for them.=20 [[[ ]]]=20 [EMAIL PROTECTED]&*=20 ()_+|{}:=14<>?=20 -=3D\[];',./=20 @@@ %%%=20 ### ^^^=20 ___---=20 Ho_mer=20 [EMAIL PROTECTED]&*()_+|`{}:=14<>?-=3D\[];=12,./=20 _Storage-Room=20 @ # $=20 _ - +=20 % ^ &=20