?
Solved

mySQL: Find most similar item

Posted on 2009-04-22
11
Medium Priority
?
525 Views
Last Modified: 2013-12-12
I want to find the item with the most similar characters as: 'foo'

So if these are the items:
        green
        food
        money
        pizza

the "food" should be returned.
select `NAME` from `organizations` where (NAME MOST SIMULAR TO FOO)

Open in new window

0
Comment
Question by:hankknight
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 7

Expert Comment

by:NicoJuicy
ID: 24208273
You can use the LIKE sql function ;)

You can find more info here.

Good luck
0
 
LVL 7

Expert Comment

by:NicoJuicy
ID: 24208281
select `NAME` from `organizations` where name LIKE "foo"

Forgot to mention it :)
0
 
LVL 5

Expert Comment

by:allmer
ID: 24208324
I don't think LIKE is what you are thinking of.
LIKE would require you to use placeholders and it doesn't evaluate which is the best match.
You need to do this pro grammatically.
Look at Edit distance, Leventhstein distance, Smith-Waterman or similar solutions.

Try to find a data mining tool like Orange which includes text mining.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Author Comment

by:hankknight
ID: 24208339
No, LIKE is not good enough.

I want the MOST simular match for foo.

So if these are the items:
        green
        boo
        money
        pizza

"boo" should be returned.

and:
        green
        fio
        money
        pizza

"fio" should be returned.
0
 
LVL 5

Expert Comment

by:allmer
ID: 24208350
E.g.:
PHP:
int levenshtein ( string $str1 , string $str2 );
returns the difference between the two strings.
If you do that for all strings in the table compared to the input string and take the result(s) with the smallest number you have the best match(es)
0
 
LVL 16

Author Comment

by:hankknight
ID: 24208381
Is there anything like levenshtein available with pure sql ?
0
 
LVL 5

Expert Comment

by:allmer
ID: 24208614
Not that I know of
0
 
LVL 5

Assisted Solution

by:allmer
allmer earned 200 total points
ID: 24211990
I assume that you want to offer one or a number of alternative strings to the user if it wasn't typed correctly.
Maybe you think the database is too large and you don't want to retrieve all records and compare them using
levenshtein distance.

If no record was retrieved from the database after an initial query:
You might want to assume that the first letter(s) were typed correct then retrieve those records that are
LIKE '[A]%' replace [A] with that letter.

Using all the records compare them to the input using levenshtein distance.
You may stop if the value is below a certain threshold (say 1) if there are still too many records.

HTH


0
 
LVL 9

Accepted Solution

by:
tkalchev earned 1800 total points
ID: 24216013
0
 
LVL 5

Expert Comment

by:allmer
ID: 24217065
@tkalchev
I am also interested in how you would solve this in SQL.
May solve me lots of time ;)
Do you think this would work:

SELECT `NAME` from `organizations` where soundex(NAME) = soundex(foo) ??

Well apart from the limitations it sounds too good to be true.


0
 
LVL 9

Expert Comment

by:tkalchev
ID: 24217378
Probably, just test it :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month17 days, 10 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question