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

Ben Lee · Accepted Answer · 2010-11-23 17:17:35Z

6

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.

answered Nov 23, 2010 at 17:17

Ben Lee

53.5k15 gold badges129 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AnApprentice Over a year ago

Thanks guys. I gave this the answer as it has an explanation.

Jasdeep Singh · Accepted Answer · 2010-11-23 17:19:04Z

3

Try using

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

Notice the 'I' in LIKE

answered Nov 23, 2010 at 17:19

Jasdeep Singh

3,3364 gold badges32 silver badges47 bronze badges

Comments

Railsmechanic · Accepted Answer · 2011-02-04 09:16:36Z

0

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

answered Feb 4, 2011 at 9:16

Railsmechanic

1,0451 gold badge9 silver badges13 bronze badges

Collectives™ on Stack Overflow

Rails + PostgreSQL -Using Like

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related