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?
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.
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.
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.
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.
To sort it Case insensitive you can use ORDER BY Name COLLATE NOCASE
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With