COLLATE NOCASE
is a postfix operator (which means that it must appear directly after the expression it modifies). Therefore, in case of the IN
(and NOT IN
) operator, it must appear after the expression (which is on the left side of the IN
and NOT IN
operators):
SELECT *
FROM `table`
WHERE `column` COLLATE NOCASE IN ('foo', 'bar')
For a complete example, let's suppose you have the following SQLite table:
CREATE TABLE `customer` (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(25) NOT NULL
);
INSERT INTO customer(`name`)
VALUES
('John'),
('john'),
('JOHN'),
('Wayne'),
('wayne'),
('WAYNE');
To select all case-insensitive matches in the IN
clause, you could do the following:
SELECT *
FROM `customer`
WHERE `name` COLLATE NOCASE IN ('john', 'wayne');
This would yield the following result:
+----+-------+
| id | name |
+----+-------+
| 1 | John |
| 2 | john |
| 3 | JOHN |
| 4 | Wayne |
| 5 | wayne |
| 6 | WAYNE |
+----+-------+
This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.