Search code examples
androidsqlsqliteandroid-sqlite

how to make my SQlite query case insensitive?

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 .

Solution

  • 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().