CODEWITHSUNDEEP
mysql
user1091856
user1091856

Reputation: 3158

MySQL: Selecting most similar value?

So I have a table with peoples names. I need to code a query that selects the person with the name most similar to the given one.

For example: 

SELECT * FROM people WHERE name='joen'

'joen' doesnt exist in the table, so it will return John, which exists in the table.

What's the MySQL command for this?

Upvotes: 1

Views: 3929

Answers (4)

Gautham
Gautham

Reputation: 1

The LIKE Keyword might help you

SELECT * FROM people WHERE name LIKE 'jo%'

Selects users where name starts with "jo"

If you're programatticaly changing the query, you can check for the "result" and query the database again with the new Query by reducing the characters in the "name" specified.

SELECT * FROM people WHERE name LIKE 'jones%'

SELECT * FROM people WHERE name LIKE 'jone%'

SELECT * FROM people WHERE name LIKE 'jon%'

SELECT * FROM people WHERE name LIKE 'jo%'

Upvotes: -1

Alp
Alp

Reputation: 29739

The Levenshtein algorithm computes the "distance" between words: Levenshtein MySQL function

You can use it like this if you add that function:

SELECT * FROM people WHERE levenshtein('joen', `name`) BETWEEN 0 AND 4;

Upvotes: 2

mpen
mpen

Reputation: 283043

I'm not an expert on this, but this is no trivial matter. By "similar" do you mean it might be one or 2 letters off, or do you mean "Jon" should match "Jonathan" and "Bill" should match "William"?

If the latter, you might want to find or build a table that maps names/nicknames to eachother, and do a search on that.

If it's misspellings, Levenshtein might be of assistance, but I don't know how you'd integrate that in an SQL query.

Upvotes: 0

Derek
Derek

Reputation: 23278

You may be looking for SOUNDEX and SOUNDS_LIKE

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Upvotes: 2

Related Questions