how to make my SQlite query case insensitive?

Question

My original code is :

String sql = "select t.* from (select dId, " +
                    "case when substr(dWord, 1, 4) in ('das ', 'der ','die ') " +
                    "then substr(dWord, 5) else dWord end as dWord " +
                    "from german) t where t.dWord like '%' || ? || '%' " +
                    "order by instr(t.dWord, ?), t.dWord";
            c = mydb.rawQuery(sql, new String[] {search, search});

For example when I searched 'A' it show all word stared with Capital 'A' sorted alphabetically and then show words started with 'a' alphabetically. But I want to consider 'a' as 'A' and 'A' as 'a'.What should I change on my code? Edit1 = I know COLLATE NOCASE but i don't know how I use it on my code .

refer https://stackoverflow.com/questions/973541/how-to-set-sqlite3-to-be-case-insensitive-when-string-comparing — sasikumar, Nov 15 '19 at 09:51
Hi I know COLLATE NOCASE but I don't know where I should add it on my code to it works properly ? — Shayegan, Nov 15 '19 at 10:04

forpas · Accepted Answer · 2019-11-15T11:28:34.350

1

Change the last line of the statement to:

order by instr(lower(t.dWord), lower(?)), lower(t.dWord)

The function lower() will convert both the column value and the search string to lower case, to simulate case insensitive comparison inside the function instr().

edited Nov 15 '19 at 11:28

answered Nov 15 '19 at 11:23

forpas

117,400
9
23
54

Thanks alot . Is it possible to changed a code for consider these letters as similar letter also.example : 'a' as 'a' and 'ä' | 'ä' as 'ä' and 'a' And also for bellow letters as explained example . o/ö - u/ü - ss/ß - – Shayegan Nov 16 '19 at 00:36
1

SQLite is not that flexible at this issue. There are many questions here in SO about it: https://stackoverflow.com/questions/5492508/ignore-accents-sqlite3, https://stackoverflow.com/questions/2841694/how-to-sql-compare-columns-when-one-has-accented-chars, https://stackoverflow.com/questions/58874356/how-to-make-my-sqlite-query-case-insensitive/58876037?noredirect=1#comment104039856_58876037, https://stackoverflow.com/questions/16282083/how-to-ignore-accent-in-sqlite-query-android, https://stackoverflow.com/questions/19906086/problems-ordering-sqlite-by-a-column-with-accented-characters-Á – forpas Nov 16 '19 at 11:01
Maybe you can help on this case? https://stackoverflow.com/questions/59702535/how-to-write-sqlite-query-to-mysql – Shayegan Jan 12 '20 at 09:35

score 0 · Answer 2 · answered Nov 15 '19 at 10:03

0

Use the lower or upper keyword in the column you wanna oder by without case sensitive

SELECT NAME from Customers 
order By lower(NAME);

Reference

SQLite lower

answered Nov 15 '19 at 10:03

Ink Archer

126
1
8

how to make my SQlite query case insensitive?

2 Answers2

Linked