There are several ways to do case insensitive string matching in SQL. Here are two ways that I've tried and analyzed on a table that doesn't have any indices.

Option 1:


(
 LOWER(u.first_name) = LOWER('Lazy') OR 
 LOWER(u.last_name) = LOWER('Lazy') OR
 LOWER(u.first_name || u.last_name) = LOWER('Lazy')
)

Option 2:


(
 u.first_name ILIKE 'Lazy' OR 
 u.last_name ILIKE 'Lazy' OR
 u.first_name || u.last_name ILIKE 'Lazy'
)

A potentially third option is to make sure that the parameters sent to the SQL code is cooked, in this case we make the parameter into lower case before sent to the SQL code

Option 1b:


(
 LOWER(u.first_name) = 'lazy' OR 
 LOWER(u.last_name) = 'lazy' OR
 LOWER(u.first_name || u.last_name) = 'lazy'
)

Which one do you think is fastest?

The results are:


Option 1:  2.0ms - 2.5ms (average 2.25ms)
Option 1b: 2.0ms - 2.1ms (average 2.05ms)
Option 2: 1.7ms - 2.0ms (average 1.85ms)

Conclusion: the ILIKE operator method is the fastest. Not only is it faster, it also supports regular expressions.

I've always thought that the LIKE and ILIKE were sinfully slow (yet useful when time isn't an issue). I should perhaps redo these tests with an index on the first_name and last_name columns.

Comments

djo

Try

CREATE INDEX u_first_name_index ON u (lower(first_name));

or a variation thereof.

(Ah, I just noticed you wrote "without any indices". You probably already know this, then. I'll post it anyway - for the search engines.)

You could also use a shadow column maintained by a trigger, but that's an evil solution, only to be used in almost never-met circumstances.

Your email will never ever be published.

Previous:
Squeezebox + Pandora March 8, 2006 Misc. links
Next:
Carbon XEmacs installed March 14, 2006 macOS
Related by category:
I'm a GitHubber now September 21, 2021 Work
Recruiters: if you're going to lie, do it properly April 7, 2013 Work
An idea for a better timesheet tracker January 12, 2006 Work
Sorting transform function in PostgreSQL August 3, 2006 Work
Related by keyword:
Adding client-to-server sync to PissueTracker March 20, 2025 React, JavaScript, Bun
Fastest way to uniqify a list in Python August 14, 2006 Python
mincss "Clears the junk out of your CSS" January 21, 2013 Python, Web development
Connecting with psycopg2 without a username and password February 24, 2011 Python