Sql- Ignore case while searching for a string

sql

case-insensitivity

collation

wildcards

byAnton Shumikhin·Aug 6, 2024

Achieving case-insensitivity in SQL strings can be accomplished by using LOWER() or UPPER(). These functions make both target column and search term maintain the same case:

-- Here is how you find the needle in a haystack, or search something regardless of the case 😉
SELECT * FROM table WHERE LOWER(column) = LOWER('searchTerm');

Wildcard use for pattern matching

Use wildcards to match patterns that align with the search term, irrespective of case. This helps when needing to find a string that could reside anywhere within the column value:

-- You see, SQL is a lot like a detective with a magnifying glass - we can find anything even if it's hiding in the shadows!
SELECT * FROM table WHERE LOWER(column) LIKE LOWER('%partialTerm%');

Commanding COLLATE for case insensitivity

For SQL Server instances, using COLLATE with a specific collation sequence can impose case insensitivity:

-- SQL Server is not a bigot, it does not judge based on letter cases, thanks COLLATE!
SELECT * FROM table WHERE column COLLATE SQL_Latin1_General_Cp1_CI_AS = 'searchTerm';

Database nuances for fine-tuning searches

PostgreSQL and its knight: ILIKE operator

PostgreSQL eases the case-insensitivity game using ILIKE, decoding the query to a simpler form:

-- PostgreSQL's motto is 'Keep It Simple, Sherlock!'
SELECT * FROM table WHERE column ILIKE '%searchTerm%';

SQL Server's little secret: Collation settings

For SQL Server, accessing list of collations can even the tides, along with the COLLATE clause:

-- Personifying SQL Server here a bit, its like saying 'Yeah I can speak your language too!'
SELECT * FROM table WHERE column COLLATE SQL_Latin1_General_Cp1_CI_AS = 'searchTerm';

Different collation settings can be your knights in shining armor.

Making the query special with DISTINCT and COLLATE

To ensure unique case-insensitive results, DISTINCT can be tagged with COLLATE:

-- SQL's very own unique(but not always case-sensitive) mixtape!
SELECT DISTINCT column FROM table WHERE column COLLATE SQL_Latin1_General_Cp1_CI_AS LIKE '%searchTerm%';

Potential curveballs to watch out for

Incorporating these methods has its own challenges:

The nod to performance: LOWER() and UPPER() can incur processing overhead boosting your wait time.
The compatibility concern: Not every database loves ILIKE or has a default collation to ignore case.
The collation caveat: Selection of collation should be compatible to accurately compare full column data.