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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users