Rails + PostgreSQL -Using Like

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 havi开发者_如何学Cng is the first query returns 0 results, and the 2nd returns the correct result.

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

---

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.

---

Try using

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

Notice the 'I' in LIKE

---

Want to improve this post? Add citations from reputable sources by editing the post. Posts with unsourced content may be edited or deleted.

Queries with "LIKE" or "ILIKE" are pretty slow, especially for tables with many entries. I think it would be faster if you use the full text search ability of PostgreSQL.

PostgreSQL textsearch docs