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

Question

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?

score 24 · Answer 1 · answered Aug 04 '10 at 11:33

24

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

answered Aug 04 '10 at 11:33

Haris Custo

366
2
7

score 13 · Accepted Answer · edited Jul 29 '09 at 22:47

13

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;

edited Jul 29 '09 at 22:47

Sinan Ünür

116,958
15
196
339

answered Jul 29 '09 at 22:34

Dror Harari

3,076
2
27
25

2

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? – Marek Jedliński Jul 29 '09 at 23:15
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 Jul 29 '09 at 23:27
@moodforaday: Not out with built-in NOCASE collation. What you can is to redefine NOCASE collation in your application and implement sorting based on current system/user locale. Just name your custom collation "NOCASE". – wqw Jun 17 '11 at 14:45

Sinan Ünür · Answer 3 · 2009-07-29T22:52:50.373

1

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 ;-)

edited Jul 29 '09 at 22:52

answered Jul 29 '09 at 22:38

Sinan Ünür

116,958
15
196
339

2

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 Jul 29 '09 at 22:46

score 0 · Answer 4 · edited Nov 14 '12 at 07:34

0

Use this statement in your SQLite database:

PRAGMA case_sensitive_like = false

edited Nov 14 '12 at 07:34

answered Nov 08 '10 at 17:42

ademus

9
1

1

Case sensitive like is already false for ASCII characters, and case sensitive by default for unicode characters that are beyond the ASCII range. [link](http://www.sqlite.org/lang_expr.html) – D.Rosado Apr 16 '12 at 13:43

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

4 Answers4

Linked