非区分大小写字母顺序的SQLite查询[重复]

This question already has an answer here:

这个问题已经有了答案:

How to use SQL Order By statement to sort results case insensitive? 3 answers
如何使用SQL Order By语句对结果不区分大小写进行排序?3答案

All I want to do is grab the stuff in alphabetical order and ignore the capital letters.

我要做的就是按字母顺序抓取东西，忽略大写字母。

db.rawQuery("SELECT " + catName + " FROM "+tableName+" ORDER BY "+catName+" ASC COLLATE NOCASE;", null);

This is the code I'm using above, but it always gives me an SQLite exception saying that COLLATE is a syntax error.

这是我上面使用的代码，但是它总是给我一个SQLite异常，说COLLATE是一个语法错误。

android.database.sqlite.SQLiteException: near "COLLATE": syntax error: , while compiling: SELECT Artist FROM testTable COLLATE NOCASE ASC

android.database.sqlite。SQLiteException:在“COLLATE”附近:语法错误:，编译时:从testTable COLLATE NOCASE ASC中选择Artist

3 个解决方案

#1

159

COLLATE goes before the order direction:

核对前的订单方向:

db.rawQuery("SELECT " + catName 
           + " FROM " +tableName 
        +" ORDER BY "+catName+" COLLATE NOCASE ASC;", null);

But you don't need the ASC -- that's the default so you could just as well use:

但你不需要ASC——这是默认值，所以你也可以使用:

db.rawQuery("SELECT "+ catName 
            +" FROM "+ tableName 
        +" ORDER BY "+ catName +" COLLATE NOCASE;", null);

#2

add COLLATE NOCASE after orderBy String.

在orderBy字符串之后添加排序项NOCASE。

db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy + " COLLATE NOCASE ASC");

db。查询(表、列、选择、选择、groupBy、have、orderBy +“NOCASE ASC”);

here, order by ASC or DESC depends on your need.

在这里，ASC或DESC的订单取决于您的需要。

#3

This should work too I think:

我想这也应该奏效:

db.rawQuery("SELECT "+ catName 
        +" FROM "+ tableName 
    +" ORDER BY lower("+ catName +");", null);

#1

159