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 user table entry called fname = James

The problem I ran into is the first query that returns 0 results, and the second returns the correct result.

I want 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 applies only to PostgreSQL, not the SQL standard.

Answer 2

Try using

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

Pay attention to the "I" in LIKE

Answer 3

Queries with "LIKE" or "ILIKE" are rather slow, especially for tables with many records. I think it will be faster if you use PostgreSQL full-text search.

PostgreSQL Text Search Documents