Re: [sqlite] Searching with like for a specific start letter

A practical situation? Lexicographical applications and full-text applications against text corpora require indexed substring searches, including ends-with searches. (The FTS extension is not always a good fit.) I am glad that only the LIKE operator has been overridden in Adobe's version and in

Re: [sqlite] Searching with like for a specific start letter

Tim, >But did I say that GLOB uses an index if it has been overloaded? No. I >wrote that if LIKE has been overloaded, queries that contain LIKE >won't use >the index. Typically, GLOB won't have been overridden too just >because LIKE >has been overridden: the rationale for overriding the

Re: [sqlite] Searching with like for a specific start letter

Jean-Christophe, But did I say that GLOB uses an index if it has been overloaded? No. I wrote that if LIKE has been overloaded, queries that contain LIKE won't use the index. Typically, GLOB won't have been overridden too just because LIKE has been overridden: the rationale for overriding the

Re: [sqlite] Searching with like for a specific start letter

Tim, >Queries using GLOB do use the index on the column in question (i.e. >optimization is attempted) >Queries using LIKE do not use that index if the LIKE operator has been >overridden. Sorry but GLOB doesn't use an index either if LIKE/GLOB has been overloaded. This is consistent with the

Re: [sqlite] Searching with like for a specific start letter

Just to complete the thread, I decided for the following: SELECT substr(normalized,1,1) AS letter, COUNT(*) from entry group by letter order by letter; Thank you ALL! 2010/4/26 Alberto Simões : > Hello > > Thank you all for the answers. > > On Mon, Apr 26, 2010 at 12:59 PM,

Re: [sqlite] Searching with like for a specific start letter

Hello Thank you all for the answers. On Mon, Apr 26, 2010 at 12:59 PM, Black, Michael (IS) wrote: > When you say "running on the fly" do you mean running from an sqlite3 command > prompt? I mean somebody will query it and will be waiting for the answer. > Or are you

Re: [sqlite] Searching with like for a specific start letter

Edit: I meant to type "Firefox" not Firebird. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching with like for a specific start letter

I am not quite sure what it is, and why it is, that you are doubting, Jean-Christophe. Queries using GLOB do use the index on the column in question (i.e. optimization is attempted) Queries using LIKE do not use that index if the LIKE operator has been overridden. You could confirm this claim

Re: [sqlite] Searching with like for a specific start letter

At 14:31 26/04/2010, you wrote: >If the implementation of SQLite you are using overrides the LIKE operator >(as more than a few do), then SQLite will not make use of an index on the >column in question. Use the GLOB operator instead. I doubt it. GLOB is absolutely nothing more or less than an

Re: [sqlite] Searching with like for a specific start letter

Yes. If the OP's [normword] column contains proper nouns, he must normalize to lower case in order to get accurate results from GLOB. Or, if his lexicon contains proper nouns in upper case and normal nouns in lower case, then he could always leave the case intact and use GLOB to get a count of

Re: [sqlite] Searching with like for a specific start letter

Tim Romano wrote: > If the implementation of SQLite you are using overrides the LIKE operator > (as more than a few do), then SQLite will not make use of an index on the > column in question. Use the GLOB operator instead. > > For example, I have a lexicon containing 263,000 words: > > select

Re: [sqlite] Searching with like for a specific start letter

If the implementation of SQLite you are using overrides the LIKE operator (as more than a few do), then SQLite will not make use of an index on the column in question. Use the GLOB operator instead. For example, I have a lexicon containing 263,000 words: select count(*) from lexicon where

Re: [sqlite] Searching with like for a specific start letter

on Systems From: sqlite-users-boun...@sqlite.org on behalf of Alberto Simões Sent: Sun 4/25/2010 3:39 PM To: General Discussion of SQLite Database Subject: [sqlite] Searching with like for a specific start letter Hello I am running on the fly a query to count the number of word

Re: [sqlite] Searching with like for a specific start letter

On Sun, 25 Apr 2010 21:39:43 +0100, Alberto Simões wrote: >Hello > > I am running on the fly a query to count the number of > words starting with one of the 26 letters. > > I am doing the usual SELECT COUNT(term) from dictionary WHERE normword > LIKE "a%" (for the 26

Re: [sqlite] Searching with like for a specific start letter

On 25 Apr 2010, at 9:39pm, Alberto Simões wrote: > One idea is to add a column named 'letter' and SELECT COUNT(letter) > from dictionary WHERE letter = 'a'. That will be the simplest way to make a fast lookup, though it will slow down your INSERT function. You could speed it up a tiny bit

Re: [sqlite] Searching with like for a specific start letter

Alberto Simões wrote: > I am running on the fly a query to count the number of words starting > with one of the 26 letters. > > I am doing the usual SELECT COUNT(term) from dictionary WHERE normword > LIKE "a%" (for the 26 letters) > > normword is the term normalized without accents and the

[sqlite] Searching with like for a specific start letter

Hello I am running on the fly a query to count the number of words starting with one of the 26 letters. I am doing the usual SELECT COUNT(term) from dictionary WHERE normword LIKE "a%" (for the 26 letters) normword is the term normalized without accents and the like Is there any way to make