CODEWITHSUNDEEP
ruby-on-railspostgresql
AnApprentice
AnApprentice

Reputation: 111070

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 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

Upvotes: 4

Views: 2315

Answers (3)

Railsmechanic
Railsmechanic

Reputation: 1045

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

Upvotes: 0

Jasdeep Singh
Jasdeep Singh

Reputation: 3326

Try using 

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

Notice the 'I' in LIKE

Upvotes: 3

Ben Lee
Ben Lee

Reputation: 53349

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.

Upvotes: 6

Related Questions