SQLite Forum

Getting a date range using "like"
Login
Can confirm my hunch. Using default collation for field and index:

```sql
create table t ( timestamp text, data ) ;
insert into t values ( '2021-08-02', 'date1' ), ( '2021-08-23', 'date2' ), ( '2021-09-01', 'date3' ) ;
create index idx on t( timestamp ) ;
.eqp on
```

produces a table-scan:

```sql
-- Using default index
select * from t where timestamp like '2021-08-%' ;
QUERY PLAN
`--SCAN TABLE t
2021-08-02|date1
2021-08-23|date2
```

Replacing the index with a `COLLATE NOCASE` one:

```sql
drop index idx ;
create index idx on t( timestamp collate nocase ) ;
```

uses the index:

```sql
-- Using NOCASE index
select * from t where timestamp like '2021-08-%' ;
QUERY PLAN
`--SEARCH TABLE t USING INDEX idx (timestamp>? AND timestamp<?)
2021-08-02|date1
2021-08-23|date2
```

Alternatively, creating the original field as `COLLATE NOCASE` and using a default index:

```sql
drop table t ;
create table t ( timestamp text collate nocase, data ) ;
insert into t values ( '2021-08-02', 'date1' ), ( '2021-08-23', 'date2' ), ( '2021-09-01', 'date3' ) ;
QUERY PLAN
`--SCAN 3 CONSTANT ROWS
create index idx on t( timestamp ) ;
```

also uses the index:

```sql
-- Using NOCASE collation
select * from t where timestamp like '2021-08-%' ;
QUERY PLAN
`--SEARCH TABLE t USING INDEX idx (timestamp>? AND timestamp<?)
2021-08-02|date1
2021-08-23|date2
```