Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 551
rkb Avatar asked Jul 27 '09 15:07

rkb


People also ask

How do you make case insensitive comparisons in SQLite?

To be case insensitive on firstname , write this: select * from tbl where firstname='john' COLLATE NOCASE and lastname='doe' . It's specific to that one column, not the entire where clause.

Is SQLite select case sensitive?

Important Note: SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.

What is collate Nocase in SQLite?

SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM. BINARY - Compares string data using memcmp(), regardless of text encoding. NOCASE - It is almost same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed.

How do you change the table collation?

You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.


2 Answers

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

like image 59
Haris Custo Avatar answered Oct 07 '22 09:10

Haris Custo


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;

like image 29
Dror Harari Avatar answered Oct 07 '22 10:10

Dror Harari