How to Use COLLATE NOCASE With SQLite IN Operator?

Find out how to make SQLite COLLATE NOCASE work with 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 |
+----+-------+

Hope you found this post useful. It was published . Please show your love and support by sharing this post.