bug report (INSTR() ignores NOCASE on columns)

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

bug report (INSTR() ignores NOCASE on columns)

Stephan Senzel
INSTR() ignores NOCASE on columns

-----------------------

example:

SELECT * FROM table WHERE INSTR(column, ' castle ') > 0

returns datasets with 'castle' only, without 'Castle', even if the
column is set to NOCASE

-----------------------

LIKE doesn't have this problem, works well

SELECT * FROM table WHERE column LIKE '% castle %'

returns 'castle' and 'Castle' when column is set to NOCASE
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: bug report (INSTR() ignores NOCASE on columns)

Jose Isaias Cabrera-4

Stephan Senzel, on Sunday, February 2, 2020 08:12 AM, wrote...

>
> INSTR() ignores NOCASE on columns
>
> -----------------------
>
> example:
>
> SELECT * FROM table WHERE INSTR(column, ' castle ') > 0
>
> returns datasets with 'castle' only, without 'Castle', even if the
> column is set to NOCASE

True statement with v3.31.0:
12:25:41.10>sqlite3
SQLite version 3.31.0 2020-01-22 18:38:59
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t0(a string collate nocase);
sqlite> insert into t0 values ('In my castle I have...');
sqlite> insert into t0 values ('In my castle I have had...');
sqlite> insert into t0 values ('In my castle I''ve never had...');
sqlite> insert into t0 values ('In my Castle I have...');
sqlite> select a from t0 where INSTR(a,' castle') > 0;
In my castle I have...
In my castle I have had...
In my castle I've never had...
sqlite>


> LIKE doesn't have this problem, works well
>
> SELECT * FROM table WHERE column LIKE '% castle %'
>
> returns 'castle' and 'Castle' when column is set to NOCASE

Also true with v3.31.0:
sqlite> select a from t0 where a LIKE '% castle%';
In my castle I have...
In my castle I have had...
In my castle I've never had...
In my Castle I have...
sqlite>

Just making sure... :-)

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users