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

In SQLite3, string comparisons are case-sensitive by default. If you need case-insensitive comparisons in queries, you can use SQLite's built-in function COLLATE NOCASE.

For example, suppose we have a database table Users with a column named Username. If you want to find users with the username "admin" in a case-insensitive manner, you can write the following SQL query:

sql
SELECT * FROM Users WHERE Username COLLATE NOCASE = 'admin';

This query matches all records in the Username column with variations such as "admin", "Admin", "aDmin", etc.

Additionally, if you frequently need to perform case-insensitive comparisons on a column, you can specify the column to use the NOCASE collation rule when creating the table. For example:

sql
CREATE TABLE Users (
    ID INTEGER PRIMARY KEY,
    Username TEXT COLLATE NOCASE
);

This way, all comparisons on the Username column are case-insensitive by default.

Using COLLATE NOCASE effectively handles case-insensitive comparisons in queries, which is ideal for scenarios like usernames or email addresses where data matching issues may arise due to inconsistent case.