I started looking into making my “blog” software use case-insensitive searching. I thought that would make the “Search” facility for this site much more powerful.
The secret to Postgresql case-insensitive searching with SQL
SELECT queries is to use regular expressions. If you’re used to standard Unix/POSIX regular expressions, the implementation is pretty easy as well. Instead of using the standard database
LIKE operator, the solution is to use the Postgres
Postgres case-insensitive search example
Here’s a simple Postgresql case-insensitive search example:
SELECT subject FROM topics WHERE subject ~* 'eclipse';
This Postgres query returns all matches from the
subject field in the
topics table that contain the phrase
'eclipse', but because it is a case-insensitive search, it also matches things like
Eclipse and even
As a word of warning I don’t know how this works with indexes and table-scanning, so if you’re performing a query on a large database table you’ll want to be careful and test the performance of this approach.
In summary, if you’re looking for a way to perform a case-insensitive SQL
SELECT query using Postgresql, I hope this is helpful.