Rails + PostgreSQL -Using Like

Question

I have the following two queries:

SELECT users.* FROM "users" WHERE (fname || lname LIKE '%james%')

SELECT users.* FROM "users" WHERE (fname || lname LIKE '%James%')

I have a record in the User Table with fname = James

The problem I'm having is the first query returns 0 results, and the 2nd returns the correct result.

I want the LIKE to be case insensitive. Ideas? Thanks

Answer 1

SELECT users.* FROM "users" WHERE (fname || lname ILIKE '%james%')

ILIKE = case-insenstive LIKE. Note that this is specific to PostgreSQL, and not a SQL standard.