Re: [sqlite] The LIKE operator and Swift

_ characters in your input before fencing with %, unless your input is itself a LIKE pattern. <https://sqlite.org/lang_expr.html#like> -- J. King _______ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.o

Re: [sqlite] The LIKE operator and Swift

You can build your pattern in Swift, and use a single parameter: let queryString = "select name, phone, street, city, state from phone where name like '?'" let pattern = "%\(name)%" if sqlite3_bind_text(stmt, 1, pattern, -1, SQLITE_TRANSIENT) != SQLITE_OK {do whatever} For a more

[sqlite] The LIKE operator and Swift

I am just now getting around to learning Swift and XCode. I am having a problem with 'LIKE'. When I do this: let queryString = "select name, phone, street, city, state from phone where name like '%?%'" And then this: if sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT) != SQLITE_OK {do

Re: [sqlite] database like file archive

On Tue, 27 Aug 2019 at 21:57, Peng Yu wrote: > I haven't found an archive format that allows in-place delete (I know > that .zip, .7z and .tar don't). This means that whenever delete is > needed, the original archive must be copied first. This can be > problematic when the archive is large and

Re: [sqlite] database like file archive

On 8/27/19, Jens Alfke wrote: > is [SQLite] engineered with the > assumption that a database file may be malicious, or is the assumption > "garbage in, garbage out"? https://www.sqlite.org/security.html https://www.sqlite.org/testing.html Our intent is that SQLite database files are secure in

Re: [sqlite] database like file archive

On Tuesday, 27 August, 2019 14:40, Jens Alfke wrote: >> On Aug 27, 2019, at 12:21 PM, Keith Medcalf wrote: >> Everything that has been touched by a third-party is inherently >> untrustworthy. Thus it is and thus it has always been. > Yes. I have a lot of experience with network coding and

Re: [sqlite] database like file archive

> On Aug 27, 2019, at 12:21 PM, Keith Medcalf wrote: > > Everything that has been touched by a third-party is inherently > untrustworthy. Thus it is and thus it has always been. Yes. I have a lot of experience with network coding and security, so I'm aware of this, thanks. My question

Re: [sqlite] database like file archive

On Tuesday, 27 August, 2019 12:47, Jens Alfke wrote: >Archive files often get transferred between people. Using this format >for that purpose would involve opening and reading untrusted SQLite >database files. Is that safe? Could maliciously corrupting the schema >or other metadata of a

Re: [sqlite] database like file archive

On 27 Aug 2019, at 7:47pm, Jens Alfke wrote: > Archive files often get transferred between people. Using this format for > that purpose would involve opening and reading untrusted SQLite database > files. Is that safe? Could maliciously corrupting the schema or other > metadata of a database

Re: [sqlite] database like file archive

> On Aug 27, 2019, at 7:06 AM, Philip Bennefall wrote: > > There is the sqlar archive format, which you can test using the official > sqlite3 command line shell. There is also a library for it as part of the > Sqlite3 repository. "An SQLite Archive is an ordinary SQLite database file that

Re: [sqlite] database like file archive

> The standard "sqlite3" command-line tool will read and write SQLite > archive files. See the documentation at > https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line OK. So there is basically no need to install the sqlar command since all features from the sqlar

Re: [sqlite] database like file archive

The earliest version of the shell which ships with the archive support is 3.22.0, according to the page I linked to. If you have an earlier version you could simply grab the Mac OSX precompiled binaries from the download page on sqlite.org and you'll be good to go. Kind regards, Philip

Re: [sqlite] database like file archive

On 8/27/19, Peng Yu wrote: > > How to install it? In homebrew's sqlite package, I don't find sqlar. I > use Mac OS X. > The standard "sqlite3" command-line tool will read and write SQLite archive files. See the documentation at

Re: [sqlite] database like file archive

> There is the sqlar archive format, which you can test using the official > sqlite3 command line shell. There is also a library for it as part of > the Sqlite3 repository. > > https://www.sqlite.org/sqlar.html > https://sqlite.org/sqlar/doc/trunk/README.md This is good to know. How to install

Re: [sqlite] database like file archive

https://sqlite.org/sqlar/doc/trunk/README.md On Tue, Aug 27, 2019 at 3:57 PM Peng Yu wrote: > Hi, > > I haven't found an archive format that allows in-place delete (I know > that .zip, .7z and .tar don't). This means that whenever delete is > needed, the original archive must be copied first.

Re: [sqlite] database like file archive

There is the sqlar archive format, which you can test using the official sqlite3 command line shell. There is also a library for it as part of the Sqlite3 repository. https://www.sqlite.org/sqlar.html Kind regards, Philip Bennefall On 8/27/2019 3:56 PM, Peng Yu wrote: Hi, I haven't found

[sqlite] database like file archive

Hi, I haven't found an archive format that allows in-place delete (I know that .zip, .7z and .tar don't). This means that whenever delete is needed, the original archive must be copied first. This can be problematic when the archive is large and the file to delete is small. Something along the

Re: [sqlite] UTF8 LIKE stranges

Vlczech - Tomáš Volf wrote: > CREATE TABLE people ( > firstname TEXT, > surname TEXT > ); > INSERT INTO people('Tomáš', 'Surname'); > > "SELECT * FROM people WHERE firstname LIKE ?" > For binding I use: sqlite3_bind_text(stmt, 1, name.c_str(), -1, > SQLITE_STATIC); SQLITE_STATIC works only

[sqlite] UTF8 LIKE stranges

Hello, I have some strange behaviout in LIKE query in SQLite. Letš see some very simplified example:   Let's have a table CREATE TABLE people (   firstname  TEXT,   surname TEXT ); and in it following data: INSERT INTO people('Tomáš', 'Surname'); created by sqlite3_exec() function.     Then I

Re: [sqlite] UTF8 LIKE stranges

Sorry for "spam", I hope that previous HTML form of mail (with bullet lists) will be readable. There is, for sure and better readability for non-HTML clients, plain text version of previous mail:   Hello, I have some strange behaviout in LIKE query in SQLite. Letš see some very

[sqlite] The LIKE optimization breaks user defined like functions

Domingo Alvarez Duarte wrote: > Today I decided to see why my applications stop using index when using > my user defined "like" function The optimization replaces the LIKE with two comparisons. Installing a user-defined function implies that your new LIKE works differently, so this optimization

[sqlite] The LIKE optimization breaks user defined like functions

Hello ! Today I decided to see why my applications stop using index when using my user defined "like" function and it seems that during the creation of the "LIKE" optimization sqlite3 made some special settings to the builtin "like" function but didn't exposed it to third party developers. I

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

On 2015-03-06 03:19 PM, Richard Hipp wrote: > Yikes! Actually (1) cannot be determined for normal (non-virtual) > tables either because the value could be a BLOB even if the column > affinity is TEXT. And so the current LIKE optimization is not valid > *ever*. See ticket

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

Nachricht- Von: R.Smith [mailto:rsmith at rsweb.co.za] Gesendet: Freitag, 06. M?rz 2015 14:25 An: sqlite-users at mailinglists.sqlite.org Betreff: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On 2015-03-06 03:19 PM, Richard Hipp wrote: > Yikes! Actually

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

On Fri, Mar 6, 2015 at 1:21 PM, Hick Gunter wrote: > And then there remain to be considered the effects of the pragma > CASE_SENSITIVE_LIKE > Good point. But that's no different from the case when an application overrides the LIKE behavior via a custom function, and the vtable can similarly

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

BestIndex Bug in system.data.sqlite/sqlite with 'like' On Fri, Mar 6, 2015 at 10:12 AM, R.Smith wrote: > On 2015-03-06 09:42 AM, Dominique Devienne wrote: > >> On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: >> >> >>> The LIKE operator can be overridden by the

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

ics. For that reason, virtual tables are unable to >> optimize using LIKE since they have no way of knowing what it will do. >> >> Works as designed. >> > Sure. But SQLite knows whether LIKE is overriden by the application or not, > so shouldn't it only hide LIKE from

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

he application wants - it is not compelled to follow >>> standard SQL semantics. For that reason, virtual tables are unable to >>> optimize using LIKE since they have no way of knowing what it will do. >>> >>> Works as designed. >>> >>> Sure. But SQLite

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

mpelled to follow > standard SQL semantics. For that reason, virtual tables are unable to > optimize using LIKE since they have no way of knowing what it will do. > > Works as designed. > Sure. But SQLite knows whether LIKE is overriden by the application or not, so shouldn't it onl

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

On 3/6/15, Dominique Devienne wrote: > > Sure. But SQLite knows whether LIKE is overriden by the application or not, > so shouldn't it only hide LIKE from the virtual table when it detects it, > instead of wholesale preventing the vtable from optimizing the "normal > semant

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

in system.data.sqlite/sqlite with 'like' On Mar 5, 2015, at 12:30 PM, Mike Nicolino wrote: > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a > bug with Virtual Tables. Queries using 'like' in the where clause are not > getting the like clause passed to

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, March 05, 2015 10:46 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On 3/5/15, Mike Nicolino wrote: > I'm using System.Data.SQLite v

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a bug with Virtual Tables. Queries using 'like' in the where clause are not getting the like clause passed to BestIndex as a query constraint. Specifically: - Simple query: select * from foo where name like

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

On 3/5/15, Mike Nicolino wrote: > Incidentally, this used to 'work' in a much older version of SQLite, though > the semantics may not have been correct in all cases. Version 3.7.7.1, > ended up transforming like to of pair of constraints in the "like 'a%'" case > which were passed to BestIndex.

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

On 3/5/15, Mike Nicolino wrote: > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a > bug with Virtual Tables. Queries using 'like' in the where clause are not > getting the like clause passed to BestIndex as a query constraint. > Specifically: > > > - Simple

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

On Mar 5, 2015, at 12:30 PM, Mike Nicolino wrote: > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a > bug with Virtual Tables. Queries using 'like' in the where clause are not > getting the like clause passed to BestIndex as a query constraint. > Specifically:

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 21 jul 2013, om 18:06 heeft Simon Slavin het volgende geschreven: On 21 Jul 2013, at 4:41pm, E.Pasma wrote: Is a change in SQLite imaginable such that column expressions are not re-evaluated with each reference to the column alias? ... ... This is partly because

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On Jul 21, 2013, at 7:47 PM, Igor Tandetnik wrote: > This query is in fact perfectly legal. It's OK to refer to column aliases in > ORDER BY clause. Perhaps in SQLite, yes. select 1 as a order by 1; select 1 as a order by a; select x as a from ( select 1 as x ) order by

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 7/21/2013 12:06 PM, Simon Slavin wrote: One of the problems with this is that it's not standard SQL. You're not meant to be able to refer to column aliases inside the SELECT that defines them. For instance SELECT yearJoined AS y, ageWhenJoined AS a, (y-a) AS yob FROM members is not

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 21-07-2013 12:01, E.Pasma wrote: Op 21 jul 2013, om 11:27 heeft Mikael het volgende geschreven: Hi Igor, Ah I just noticed how you wrote your query and it delivers for it indeed. Here's an arbitrary example verifying its works. Neat - thanks! sqlite3 test.sqlite create table categories

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

-- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Clemens Ladisch > Sent: Sunday, 21 July, 2013 07:43 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as > c; " possible whatsoev

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 21 Jul 2013, at 4:41pm, E.Pasma wrote: > Is a change in SQLite imaginable such that column expressions are not > re-evaluated with each reference to the column alias? > This could also improve queries that use aliases only in the order by clause, > like > select id,

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 21 jul 2013, om 15:43 heeft Clemens Ladisch het volgende geschreven: RSmith wrote: On 2013/07/21 12:01, E.Pasma wrote: Only the execution plan of this query is not optimal: 0|0|0|SCAN TABLE categories (~100 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SCAN TABLE ot AS ot1

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

RSmith wrote: > On 2013/07/21 12:01, E.Pasma wrote: >> Only the execution plan of this query is not optimal: >> 0|0|0|SCAN TABLE categories (~100 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 >> 1|0|0|SCAN TABLE ot AS ot1 (~33 rows) >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 >>

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Well yes, the plan does not read like one would expect an optimal plan to read like - but to the purpose of the original request there is no more-optimal a plan, is there?. The entire column used to sort by is made up on the spot and therefore temp BTrees are needed and all the other quirks, as

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 21 jul 2013, om 11:27 heeft Mikael het volgende geschreven: Hi Igor, Ah I just noticed how you wrote your query and it delivers for it indeed. Here's an arbitrary example verifying its works. Neat - thanks! sqlite3 test.sqlite create table categories (id number); insert into

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Hi Igor, Ah I just noticed how you wrote your query and it delivers for it indeed. Here's an arbitrary example verifying its works. Neat - thanks! sqlite3 test.sqlite create table categories (id number); insert into categories (id) values (5),(10),(15); create table ot (v number); insert into

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 7/20/2013 7:04 PM, Mikael wrote: C is not a present column, but is the result of an expression involving A and B (namely, the expression A / B). Yes. And this is notable because...? Can you please take this into consideration and update the query you proposed? :)) In what way do you

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Hi Igor, C is not a present column, but is the result of an expression involving A and B (namely, the expression A / B). Can you please take this into consideration and update the query you proposed? :)) Thanks, Mikael 2013/7/20 Igor Tandetnik > On 7/20/2013 9:54 AM,

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 7/20/2013 3:29 PM, E.Pasma wrote: Op 20 jul 2013, om 16:13 heeft Igor Tandetnik het volgende geschreven: select id, a, b, a/b as c from ( SELECT id, (SELECT [very complex subselect here, that uses categories.id as input]) AS a, (SELECT [another very complex subselect here, that uses

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Op 20 jul 2013, om 16:13 heeft Igor Tandetnik het volgende geschreven: On 7/20/2013 9:54 AM, Mikael wrote: So again, SELECT id, (SELECT [very complex subselect here, that uses categories.id as input]) AS a, (SELECT [another very complex subselect here, that uses categories.id as input])

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

To: General Discussion of SQLite Database; luu...@gmail.com Subject: Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever? Ah I realize now I didn't write it out in the example, but by thing and thing2 I just allegorically wanted to represent a *ve

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 7/20/2013 9:54 AM, Mikael wrote: So again, SELECT id, (SELECT [very complex subselect here, that uses categories.id as input]) AS a, (SELECT [another very complex subselect here, that uses categories.id as input]) AS b, a / b AS c FROM categories ORDER BY c; select id, a, b, a/b as c from

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Ah I realize now I didn't write it out in the example, but by thing and thing2 I just allegorically wanted to represent a *very complex* subselect, so here we go more clearly: Inlining this subselect's SQL expression in the "A / B" part would make it need to execute once more, which would make it

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

On 20-07-2013 15:26, Mikael wrote: SELECT id, (SELECT thing FROM othertable WHERE othertable.something = categories.id) AS a, (SELECT thing2 FROM othertable2 WHERE othertable2.something2 = categories.id) AS b, a / b AS c FROM categories ORDER BY c; Is there any way whatsoever to do this?

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Hi Clemens! Hmm. Let's see how this fits into the bigger picture: I have a table "categories". I'm doing a select for each of its id:s i.e: SELECT id FROM categories; Now, what I want to do is that I want to do two separate subselects that use categories.id for the respective row the

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Mikael wrote: > Is anything like "select 7 as a, 8 as b, a / b as c;" possible? Not directy, but you could use a subquery: SELECT *, a / b AS c FROM (SELECT 7 AS a, 8 AS b); Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Hi! Is anything like "select 7 as a, 8 as b, a / b as c;" possible? I.e., I select one value into one column and another into another (it's a subselect, expression etc.), and then I want to generate a separate column that's an expression involving both the earlier two generated values. Possibly

Re: [sqlite] simple like query optimization

Here, a,b,d,e,f,u and v are variable length strings. delimiter '/ ' is constant. On Fri, Jan 13, 2012 at 12:22 PM, Durga D wrote: > Hi All, > > I have a table like this: > > h t0info( h is primary key, t0info is unique) > --- > > 1/a/d/u > 2

[sqlite] simple like query optimization

Hi All, I have a table like this: h t0info( h is primary key, t0info is unique) --- 1/a/d/u 2/a/e/u 3/a/f/u 4/a/g/v 5/a/b/c/d/e/f input is : /a output should be: d, e f, g and b I am doing this way: select t0info from t0 where t0info like '/a%';

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

On Fri, Nov 11, 2011 at 1:16 PM, Simon Slavin wrote: > On 11 Nov 2011, at 6:09pm, Nico Williams wrote: >> blob = blob should be a binary comparison >> >> blob = string should be a string comparison >> >> blob LIKE pattern should either treat the blob as a string or not, but

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

On 11 Nov 2011, at 6:09pm, Nico Williams wrote: > blob = blob should be a binary comparison > > blob = string should be a string comparison > > blob LIKE pattern should either treat the blob as a string or not, but > I don't see why either behavior should imply that blob = blob should > be

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

On Fri, Nov 11, 2011 at 11:55 AM, Yuriy Kaminskiy wrote: > One way or other, "=", "LIKE" and "GLOB" results should be consistent. > If string is NUL-terminated, "=" should ignore everything after NUL. > If string is length-terminated, "LIKE" should not ignore bytes after NUL.

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

Simon Slavin wrote: > On 9 Nov 2011, at 8:03pm, Yuriy Kaminskiy wrote: > >> Look at: SELECT hex(X'1245005679'),hex(X'1245001234'); >> >> And compare: SELECT X'1245005679' LIKE X'1245001234'; 1 -- incorrect SELECT >> X'1245005679' = X'1245001234'; 0 -- correct SELECT X'1245005679' > >>

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

Roger Andersson wrote: > On 11/09/11 19:42, Yuriy Kaminskiy wrote: >> Paul Corke wrote: >>> On 09 November 2011 15:32, hmas wrote: >>> sqlite> select hex(foocol) from footable where foocol like '98012470700566'; 39393939393830313234373037303035363600 >>> It looks like there's

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

On 11/09/11 19:42, Yuriy Kaminskiy wrote: Paul Corke wrote: On 09 November 2011 15:32, hmas wrote: sqlite> select hex(foocol) from footable where foocol like '98012470700566'; 39393939393830313234373037303035363600 It looks like there's an extra 00 on the end. x'3900' != x'39' That

[sqlite] [bug] LIKE operator ignores rest of string after NUL character (was: select ... where [=] or [like])

Paul Corke wrote: > On 09 November 2011 15:32, hmas wrote: > >> sqlite> select hex(foocol) from footable where foocol like >> '98012470700566'; >> 39393939393830313234373037303035363600 > > It looks like there's an extra 00 on the end. > > x'3900' != x'39' That said, it seems LIKE

Re: [sqlite] database like ms northwind

As i understand it, Northwind is simply an example of Ms access (populated db). So, if you need an example of access, buy access and you get Northwind; If, on the other hand, you dont buy it, you cant use access at all (unless by broking the law); If you are looking for something like Northwind

[sqlite] database like ms northwind

i want to make a databse like Northwind of ms Access.from where i can get guidance or download something similar? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

Were you able to solve the problem? What solution have to chosen? BR, Fedor I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or sqlite expert, so I might be thinking about it all wrong. I have something like a (read-only) address book/rolodex, with interactive

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

Subject: EXTERNAL:Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way? First, permit me a little rant. As a user, I dislike this kind of incremental search feature if there's no easy way to toggle it or to configure it and the lis

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

First, permit me a little rant. As a user, I dislike this kind of incremental search feature if there's no easy way to toggle it or to configure it and the list of items will be large enough to cause a typing lag. The feature can become an intrusive nuisance, the opposite of what is intended.

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts wrote: > On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess wrote: >> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts wrote: >>> FTS3 only searches full terms/words by default, but I think if I built a

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

Have you not consider loading the whole rows into memory array and use simple string search or regexp? I'm sure 10,000 records could be search a blink. best regards, Radzi. On 6-Aug-2010, at 3:42 AM, Sam Roberts wrote: > I'd appreciate any suggestions on good ways to do this, I'm neither an SQL

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess wrote: > On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts wrote: >> FTS3 only searches full terms/words by default, but I think if I built a >> custom >> tokenizer that returned all the suffix trees for a name: > >

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts wrote: > FTS3 only searches full terms/words by default, but I think if I built a > custom > tokenizer that returned all the suffix trees for a name: FTS3 can do prefix searches, MATCH 'a*'. Also, it aimed to support multiple

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Fri, Aug 6, 2010 at 6:11 AM, Adam DeVita wrote: > A variant on Simon's plan. > Are the 10,000 rows static, slowly changing, or frequently changing? Never change, it's read-only. >  Does > it make sense to pre-calculate some counts at the time data is loaded? >  Is >

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

A variant on Simon's plan. Are the 10,000 rows static, slowly changing, or frequently changing? Does it make sense to pre-calculate some counts at the time data is loaded? Is this memory constrained so much that you can't afford 1 or 2 MB to let you look up based on ints? (I'm assuming that one

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On 5 Aug 2010, at 10:03pm, Sam Roberts wrote: > But do you think the section would make the counting faster? I think > I'd have to get the row counts like this, which would still do the > slow full table scan: > > select section, count(*) from my_table where name like '%e%' group by >

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Thu, Aug 5, 2010 at 1:37 PM, Simon Slavin wrote: > > On 5 Aug 2010, at 8:42pm, Sam Roberts wrote: > >> select substr(name,1,1), count(*) from my_table where name like '%e%' >> group by substr(name,1,1); > > If you are constantly going to be using the first character of

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On 5 Aug 2010, at 8:42pm, Sam Roberts wrote: > select substr(name,1,1), count(*) from my_table where name like '%e%' > group by substr(name,1,1); If you are constantly going to be using the first character of the name like that, give it a column of its own with its own index. Simon.

[sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or sqlite expert, so I might be thinking about it all wrong. I have something like a (read-only) address book/rolodex, with interactive searching. As users type into the search box, I need to first know for each section

Re: [sqlite] NOT LIKE

Try this instead: SELECT * FROM suppliers WHERE (NOT supplier_name LIKE '%blabla%'); RBS > Does anybody know wether the "NOT LIKE" condition such as > > SELECT * FROM suppliers > WHERE supplier_name NOT LIKE '%blabla%'; > > works in sqlite? > I used it in a query but it resulted in

[sqlite] NOT LIKE

Does anybody know wether the "NOT LIKE" condition such as SELECT * FROM suppliers WHERE supplier_name NOT LIKE '%blabla%'; works in sqlite? I used it in a query but it resulted in inconsistent data. Thanks ___ sqlite-users mailing list

Re: [sqlite] Parenthetical LIKE query ??

On 1/4/09, Ben Marchbanks wrote: > SELECT * FROM `pagesText` WHERE pageText LIKE ( "%muffler%" , "%clamp%" ) > > Is there a nice way to write a query using parenthetical LIKE ? afaik, there is no option for using LIKE in the manner of IN (as you are trying to do above). You

Re: [sqlite] Parenthetical LIKE query ??

"Ben Marchbanks" wrote in message news:49615144.7090...@alqemy.com > SELECT * FROM `pagesText` WHERE pageText LIKE ( "%muffler%" , > "%clamp%" ) > > Is there a nice way to write a query using parenthetical LIKE ? SELECT * FROM pagesText WHERE pageText LIKE '%muffler%' OR

[sqlite] Parenthetical LIKE query ??

SELECT * FROM `pagesText` WHERE pageText LIKE ( "%muffler%" , "%clamp%" ) Is there a nice way to write a query using parenthetical LIKE ? Queries like this can get quite long otherwise Any suggestions ? *Ben Marchbanks* www.magazooms.com Signature Email:

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

I'm aware that ICU is able to provide a very general solution, but I'm wondering about two other options: (1) Just as an OS abstraction layer is in place for I/O, wouldn't it be possible to use an OS abstraction layer for L14N? So that for example uppercasing is forwarded to

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

a class named "RegexMatcher", see > >> http://icu-project.org/apiref/icu4c/classRegexMatcher.html). > >> So, it should be relatively easy to replace the like() - function in > >> sqlite (see http://www.sqlite.org/lang_corefunc.html#like and > >> http

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

tool to handle codepages, and also >> supports regular expressions (using a class named "RegexMatcher", see >> http://icu-project.org/apiref/icu4c/classRegexMatcher.html). >> So, it should be relatively easy to replace the like() - function in >> sqlite (see

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

er.html). > So, it should be relatively easy to replace the like() - function in > sqlite (see http://www.sqlite.org/lang_corefunc.html#like and > http://www.sqlite.org/c3ref/create_function.html) > http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt=

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

Hi all, the ICU project is a very powerful tool to handle codepages, and also supports regular expressions (using a class named "RegexMatcher", see http://icu-project.org/apiref/icu4c/classRegexMatcher.html). So, it should be relatively easy to replace the like() - function in sqlite

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

Thomas Mittelstaedt schrieb: > Hallo, > > Just did a search on my database using > SELECT * FROM ku2008 where "Empfaenger 1" like '%köck%'; > > and nothing was found. Doing a SELECT * FROM ku2008 where "Empfaenger 1" > like '%kÖck%'; with the capital umlaut did find the record. > The data is

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

Hello Thomas, I have the same problem. There is no readily available function for converting utf-8 characters outside 7-bit-Ascii from lower to upper, so sqlite does not use one. To achieve this, you have to write your own function and/or incorporate something like ICU into your project. I

[sqlite] bug? like-search with german umlaut is case-sensitive, should not be

Hallo, Just did a search on my database using SELECT * FROM ku2008 where "Empfaenger 1" like '%köck%'; and nothing was found. Doing a SELECT * FROM ku2008 where "Empfaenger 1" like '%kÖck%'; with the capital umlaut did find the record. The data is utf-8! my sqlite version is 3.5.9 on ubuntu

Re: [sqlite] NOT LIKE statement

Discussion of SQLite Database Subject: Re: [sqlite] NOT LIKE statement You seemed to be expecting that name NOT LIKE 'InternetGatewayDevice.%.[1-9]' AND name NOT LIKE 'InternetGatewayDevice.%.[^1-9] would exclude some of the rows you are getting. This is incorrect. The only wild cards affecting

Re: [sqlite] NOT LIKE statement

"P Kishor" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > You are expecting the LIKE (or NOT LIKE) operator to behave like a > RegExp operator. No, it doesn't. It just has a single wildcard > specifier, the % sign. Two of them: % (a sequence of zero or more of arbitrary

Re: [sqlite] NOT LIKE statement

es > > Sent: Wednesday, June 25, 2008 4:40 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] NOT LIKE statement > > I can get your expected results from the data you have given, as shown > below: > > sqlite> CRE

Re: [sqlite] NOT LIKE statement

mes > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies > Sent: Wednesday, June 25, 2008 4:40 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] NOT LIKE statement > > I can get your expected results from th

Re: [sqlite] NOT LIKE statement

PROTECTED] On Behalf Of Simon Davies Sent: Wednesday, June 25, 2008 4:40 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] NOT LIKE statement I can get your expected results from the data you have given, as shown below: sqlite> CREATE TABLE tst( name text ); sqlite> sqlite&g

  1   2   >