CODEWITHSUNDEEP
sqlpostgresql
Andy K
Andy K

Reputation: 5044

why the or condition is not taken into account

I have the following query

SELECT url
FROM 
table_repo3
WHERE
(url LIKE '%auto%'
OR url LIKE '%automobile%' 
OR url LIKE '%voiture%'
OR url LIKE '%bagnole%'
OR url LIKE '%vehicule%'
OR url LIKE '%berline%'
OR zpages LIKE '%auto%'
OR zpages LIKE '%automobile%' 
OR zpages LIKE '%voiture%'
OR zpages LIKE '%bagnole%'
OR zpages LIKE '%vehicule%'
OR zpages LIKE '%berline%')
OR url like '%google%';

It returns me ,every rows with google and yahoo or other URL's.

If I'm using a AND instead of the last OR, I have no results.

To be able to apply the condition with google, I did the following

CREATE TEMPORARY TABLE toto 
SELECT *
FROM 
table_repo3
WHERE
(url LIKE '%auto%'
OR url LIKE '%automobile%' 
OR url LIKE '%voiture%'
OR url LIKE '%bagnole%'
OR url LIKE '%vehicule%'
OR url LIKE '%berline%'
OR zpages LIKE '%auto%'
OR zpages LIKE '%automobile%' 
OR zpages LIKE '%voiture%'
OR zpages LIKE '%bagnole%'
OR zpages LIKE '%vehicule%'
OR zpages LIKE '%berline%')
;

Then

SELECT url FROM temporary_table WHERE url LIKE '%google%';

This solution works but it is tedious and long.

Is there anything easier I can do?

TIA as always.

Upvotes: 0

Views: 87

Answers (3)

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

You can do it simply using SIMILAR TO in Postgres for multiple Like checking, Try this way.

SELECT url
  FROM 
table_repo3
  WHERE
url SIMILAR TO '%(auto|automobile|voiture|bagnole|vehicule|berline|google)%'
  OR 
zpages SIMILAR TO'%(auto|automobile|voiture|bagnole|vehicule|berline)%'

Upvotes: 2

David Faber
David Faber

Reputation: 12485

I think you want to do the following:

SELECT *
  FROM table_repo3
 WHERE url LIKE '%google%'
   AND ( url LIKE '%auto%'
      OR url LIKE '%automobile%' 
      OR url LIKE '%voiture%'
      OR url LIKE '%bagnole%'
      OR url LIKE '%vehicule%'
      OR url LIKE '%berline%'
      OR zpages LIKE '%auto%'
      OR zpages LIKE '%automobile%' 
      OR zpages LIKE '%voiture%'
      OR zpages LIKE '%bagnole%'
      OR zpages LIKE '%vehicule%'
      OR zpages LIKE '%berline%' );

But that is really not a good way of doing this. You might use a regular expression instead, but even that probably wouldn't speed things up (LIKEs with leading wildcards generally won't use indexes):

SELECT * FROM table_repo3
 WHERE url LIKE '%google%'
   AND ( url ~ '(auto)?mobile|voiture|bagnole|vehicule|berline'
      OR zpages ~ '(auto)?mobile|voiture|bagnole|vehicule|berline' );

Upvotes: 2

JustAPup
JustAPup

Reputation: 1780

Since you are adding the Google OR condition AFTER the ), what SQL does is look at the first condition, see that there are matches and return them, then look at the second condition, see that there are also matches and return them.

With an AND, both conditions have to be satisfied for SQL to return something..

If it's still unclear, you should do some research on OR and AND conditions..

Upvotes: 0

Related Questions