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

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.