Comparing text of two fields in PostgreSQL

Question

I'm trying to judge by the name of a car dealership whether a specific car is their brand or not. So with:

select car_make, dealer_name, 
case when ('%' || dealer_name || '%') ilike car_make THEN 'OnBrand'
END
from table

Ideally, when searching a car with make Nissan and a dealer named Local Nissan Dealer, the third column would contain OnBrand, but it's returning

Nissan    Local Nissan Dealer    <null>

alzee · Accepted Answer · 2015-12-16 19:21:43Z

2

You've got your wildcards backwards, you want:

case when dealer_name ilike ('%' || car_make || '%') THEN 'OnBrand'

answered Dec 16, 2015 at 19:21

alzee

1,3961 gold badge11 silver badges22 bronze badges

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

4 Comments

Matt Inwood Over a year ago

This also returns a null for that field.

alzee Over a year ago

Well the syntax is correct this way, but it's not in the question. Can you provide a complete example row and the table definition (CREATE TABLE statement).

Matt Inwood Over a year ago

Is it not possible to do this syntax in 8.1? That's the only thing I can think of that might drop your example from working. Both fields are VARCHAR and not null.

alzee Over a year ago

I will look. Can you provide the full table definition/schema please? That would be the CREATE TABLE statement. -- The case statement has been available since at least Pg 7.4 If you are not getting an actual error when issuing the query, then it is correct.

Hambone · Accepted Answer · 2015-12-16 21:24:27Z

1

This doesn't address why your particular statement isn't working (@user's solution looks good to me), but as an alternative, potentially simpler syntax, what about a regular expression?

select
  car_make, dealer_name, 
  case when dealer_name ~ car_make THEN 'OnBrand' end
from table

answered Dec 16, 2015 at 21:24

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Collectives™ on Stack Overflow

Comparing text of two fields in PostgreSQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related