Sql queries I use for SQLite
Case insensitivity in SQLite
This was an issue when I did lots of string comparision. There are two ways. First, you can use COLLATE NOCASE
in the where clause, each time. The other option is you create the column to be case-insensitive. The second option is much faster in performance
SELECT DISTINCT Repository
FROM ServiceDefinitions
WHERE Team = @Team COLLATE NOCASE
CREATE TABLE "DomainEvents" (
"Name" Text NOT NULL COLLATE NOCASE,
"Obsolete" INTEGER,
"Description" Text,
"Domain" BLOB,
"SubDomain" Text,
"Team" Text,
"TeamLead" Text,
"SubscriberCount" INTEGER,
PRIMARY KEY("Name")
);
Case-insensitive string comparison with Sqlite
Use COLLATE NOCASE
SELECT DISTINCT Repository
FROM ServiceDefinitions
WHERE Team = @Team COLLATE NOCASE
Create a table
CREATE TABLE IF NOT EXISTS ServiceDefinitions (
Name Text NOT NULL PRIMARY KEY,
Team Text,
Repository Text,
Tier Text,
Domain Text,
SubDomain Text
)
Handling duplicates
SELECT DISTINCT Domain FROM Services
The above distinct is case-sensitive, especially in sqlite. for case-insensitive, use GROUP BY.
SELECT Domain Services GROUP BY LOWER(Domain)
Wild-card match
LIKE '%.rtf'
Comments