views:

1330

answers:

3

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?

+1  A: 

The SQLite Datatypes documentation discusses user-defined collation sequences. Specifically you use COLLATE NOCASE to achieve your goal.

They give an example:

CREATE TABLE t1(
    a,                 -- default collation type BINARY
    b COLLATE BINARY,  -- default collation type BINARY
    c COLLATE REVERSE, -- default collation type REVERSE
    d COLLATE NOCASE   -- default collation type NOCASE
);

and note that:

-- Grouping is performed using the NOCASE collation sequence (i.e. values -- 'abc' and 'ABC' are placed in the same group). SELECT count(*) GROUP BY d FROM t1;

Dror Harari
Will "COLLATE NOCASE" work for any language? If not, how can one sort on a text field in a case-insensitive way, if the specific language isn't known at compile time?
moodforaday
IIRC sqlite3 is natively UTF-8, so collation should happen according to Unicode UTF-8 rules. This could, of course, not be what the user was expecting...
Michael van der Westhuizen
A: 
select * from tableNames Order by lower(Name);

Michael van der Westhuizen explains in his comment below why this is not a good way. I am leaving this answer up so as to preserve his comment and to serve as a warning to others who might have the same 'bright' idea I had ;-)

Sinan Ünür
This is not a great idea - ordering by a function where you do not have function-based indexes will result in performance and scalability problems. See the answer by Dror Harari above for the right way to do this in sqlite3.If you were using Oracle with a function-based index on lower(Name) this would be OK, but not in this case.
Michael van der Westhuizen
A: 

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

hcusto