CODEWITHSUNDEEP
mysqlsql
Mihaela
Mihaela

Reputation: 87

Query for similar phrases

Is there any way to match 'United States of America' with 'United States' in SQL?

I have the country with the name 'United States' stored in the database. The query below should retrieve the item from the database with the name 'United States'.

SELECT * FROM `countries` WHERE `name` LIKE '%United States of America%'

Thank you!

Upvotes: 1

Views: 59

Answers (2)

Seeds
Seeds

Reputation: 392

For this case, you should look into an elastic search database. It has the functionality for partial filters.

Otherwise you could do the following via dynamic sql:

  1. Split the string by spaces/delimiter
  2. Using joins, (1 is match, 0 is not match) for a given word
  3. Total the number of matches (you can pivot or target the columns via a sum operand, but its probably simpler to just sum the values in the target named columns since you're already going to use dynamic sql)
    • if the total number of matches = word count, you have an absolute match (100% match, possible mis-order)
      • For non-absolute matches, calculate the certainty by dividing the sum / number of words (absolute is 100%)
  4. Select the top (1) for this list, group by the original word, and order by the certainty.

Alternatively, you can do a what @Gordon said for a simpler, one time solution

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Is this what you want?

where 'United States of America' like concat('%', name, '%')

Upvotes: 5

Related Questions