How to Use COLLATE NOCASE With SQLite IN Operator?

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 |
+----+-------+