1

I am doing LIKE search using OR condition with multiple keywords. I am wondering is that possible to get the search result and used keywords for that result.

SELECT * FROM `LOCATIONS` WHERE `location_name` LIKE '%United Kingdom%' OR `location_name` LIKE '%France%';

The query get's the matched results with both keywords, I want to get the match is from which keyword like this!

location_name                 usedKeyword

United Kingdom UK             United Kingdom

Lille, France                 France

London, United Kingdom        United Kingdom

Event it's possible I know there is one more problem if location_name matched for both keywords. I really don't have idea. How to do this.

for example, "United Kingdom and France" is location name

Why you want to do this ?

You may have this question, I experimenting all the possible ways to get the locations country name. I have countrylist table with code and names. Using that I like to get the location names

Few other experiments

  1. reverse phrase search in zend lucene
  2. Location search query issue

PS: I know, I can run the query separatly for each country. I doing the same for cities means thousands, To minimize the number of queries, I am looking for alternative.

Community
  • 1
  • 1
Gowri
  • 16,587
  • 26
  • 100
  • 160

1 Answers1

1

Use a CASE expression:

SELECT *,
       CASE WHEN location_name LIKE '%United Kingdom%'
            THEN 'United Kingdom'
            WHEN location_name LIKE '%France%'
            THEN 'France'
       END usedKeyword
FROM `LOCATIONS`
WHERE `location_name` LIKE '%United Kingdom%'
   OR `location_name` LIKE '%France%';

Also see the corrected WHERE clause. Your WHERE clause would return all rows.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks Barmar, Do you think this query can handle the "United Kingdom and France" location name – Gowri Jul 16 '13 at 06:52
  • You could put `WHEN location_name LIKE '%United Kingdom%' AND location_name LIKE '%France%'` as the first case. But there's no easy way to generalize it to many choices, since the combinations will explode exponentially. – Barmar Jul 16 '13 at 06:54
  • Yes I understand, Do you think,can we exclude the search result for this case. – Gowri Jul 16 '13 at 06:57
  • Use `XOR` instead of `OR`. It's true if just one of the conditions is true, but not both. – Barmar Jul 16 '13 at 07:01