How to change the collation of sqlite3 database to sort case insensitively?

sql sqlite sql-order-by case-insensitive
rkb picture rkb · Jul 27, 2009 · Viewed 27k times · Source

I have a query for sqlite3 database which provides the sorted data. The data are sorted on the basis of a column which is a varchar column "Name". Now when I do the query 

select * from tableNames Order by Name;

It provides the data like this.

    Pen
    Stapler
    pencil

Means it is considering the case sensitive stuff. The way I want is as follows

    Pen
    pencil
    Stapler

So what changes should I make in sqlite3 database for the necessary results?

Related How to set Sqlite3 to be case insensitive when string comparing?

Answer

Haris Custo picture Haris Custo · Aug 4, 2010

To sort it Case insensitive you can use ORDER BY Name COLLATE NOCASE

Related questions

How to use SQL Order By statement to sort results case insensitive?

I have a SQLite database that I am trying to sort by Alphabetical order. The problem is, SQLite doesn't seem to consider A=a during sorting, thus I get results like this: A B C T a b c g …

Read More
SQLite Order By Date1530019888000

Every record in my SQLite database contains a field which contains a Date stored as a string in the format 'yyyy-MM-dd HH:mm:ss'. Is it possible to query the database to get the record which contains the most recent …

Read More
SQLite Query in non case sensitive alphabetical order

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 …

Read More