On 2015-07-23 04:56 PM, Igor Tandetnik wrote:
> On 7/23/2015 10:47 AM, Bernardo Sulzbach wrote:
>>> select * from tbl1 where substr(col1,1,1) in ('A','a') and col1 like
>>> 'a_c%';
>>
>> Just adding to Igor's answer:
>> col1 between 'a' and 'b' or col1 between 'A' and 'B'
>
> That's not quite
Since Igor's response I've been studying "The SQLite Query Planner" with
great interest.
Staffan
On Thu, Jul 23, 2015 at 4:59 PM, Bernardo Sulzbach <
mafagafogigante at gmail.com> wrote:
> > That's not quite the same. BETWEEN is inclusive of both ends. ('b'
> BETWEEN 'a' and 'b') is true.
>
>
Thanks, I wasn't aware.
On Thu, Jul 23, 2015 at 4:21 PM, Igor Tandetnik wrote:
> On 7/23/2015 10:17 AM, Staffan Tylen wrote:
>
>> Please consider this:
>>
>> create table tbl1 (col1 text primary key);
>> insert ...
>> select * from tbl1 where substr(col1,1,1)='a';
>>
>
> Make it
>
> WHERE
Please consider this:
create table tbl1 (col1 text primary key);
insert ...
select * from tbl1 where substr(col1,1,1)='a';
select * from tbl1 where col1='a';
According to explain (I'm on 3.8.6), the first select above causes a scan
of the entire table, while the second select uses the index. My
> That's not quite the same. BETWEEN is inclusive of both ends. ('b' BETWEEN
> 'a' and 'b') is true.
Yes, you are right. I'm sorry for that, just wanted to point out that
(as most would expect) .. >= 'a' and .. < 'b' would not match
uppercase beginnings.
> select * from tbl1 where substr(col1,1,1) in ('A','a') and col1 like 'a_c%';
Just adding to Igor's answer:
col1 between 'a' and 'b' or col1 between 'A' and 'B'
if you need this case sensitive behavior
On 7/23/2015 10:47 AM, Bernardo Sulzbach wrote:
>> select * from tbl1 where substr(col1,1,1) in ('A','a') and col1 like 'a_c%';
>
> Just adding to Igor's answer:
> col1 between 'a' and 'b' or col1 between 'A' and 'B'
That's not quite the same. BETWEEN is inclusive of both ends. ('b'
BETWEEN
: Bernardo Sulzbach
Sent: Thursday, July 23, 2015 10:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Possible substr() optimization?
> select * from tbl1 where substr(col1,1,1) in ('A','a') and col1 like
> 'a_c%';
Just adding to Igor's answer:
col1 between 'a' and 'b' o
On 7/23/2015 10:17 AM, Staffan Tylen wrote:
> Please consider this:
>
> create table tbl1 (col1 text primary key);
> insert ...
> select * from tbl1 where substr(col1,1,1)='a';
Make it
WHERE col1>='a' and col1 < 'b'
This should use the index.
--
Igor Tandetnik
9 matches
Mail list logo
