user6834389 user6834389 - 1 month ago 11
SQL Question

SELECT * FROM city WHERE city LIKE '%value% %value%';

i have a Database with a lot of Cities and two Cities with similar Names.

Oldenburg in Holstein and Oldenburg (Oldenburg)

I get a City from URL

Example URL:

www.example.com/cityname/oldenburg-in-holstein

www.example.com/cityname/oldenburg-oldenburg

SELECT city FROM citynames WHERE city LIKE '%oldenburg%';


I make this Statement i get Oldenburg in Holstein

SELECT * FROM citynames WHERE city LIKE '%oldenburg%' AND LIKE '%oldenburg%'';


i get Oldenburg in Holstein again but if I make this Statement

SELECT city FROM citynames WHERE city LIKE '%oldenburg% %oldenburg%';


The Statement work! And i get Oldenburg (Oldenburg)
My question is can i use this statement or no?

Answer

With

SELECT * FROM citynames WHERE city LIKE '%oldenburg%' AND LIKE '%oldenburg%'';

you are asking the same thing twice: whether the city contains the string 'oldenburg'. This is not what you want.

With

SELECT city FROM citynames WHERE city LIKE '%oldenburg% %oldenburg%';

you are looking for a city that contains 'oldenburg' then after that at some point a blank, then after that at some point again the word 'oldenburg'. So yes, this is what you want. You'll find 'Oldenburg (Oldenburg)', but not 'Oldenburg in Holstein'.

As you are getting the city names from a URL where the parts of the city name are separated with -, that will be:

SELECT city FROM citynames WHERE city LIKE CONCAT('%', REPLACE(url, '-', '%'), '%');

BTW: Obviously you are using a collation that is not case sensitive. Otherwise you'd have to use LIKE '%Oldenburg% %Oldenburg%' in order to find 'Oldenburg (Oldenburg)' or WHERE LOWER(city) LIKE ....