[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1300
  • Last Modified:

Query: How to retrieve the "closest match" for a string

I have a table called names with one field 'names'

rowdata:
queti
quest
questi
quetion

I am trying to retrieve the record 'questi' but my query input is "question", but *could* be queition

So basically I am trying to return the "best match" in my table for a given string longer than any record in the table the input variable would never match the full length of any record in the table.

0
jason987
Asked:
jason987
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you already notice the function SOUNDEX in mysql:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_soundex

now, the problem will be the "best match" ... I would say, based on your assumption, to take the "longest" value "matching":

let me suggest this (remove the LIMIT 1 at the end to see the alterntive suggestions based on the soundex and the sorting results):

select names
from rowdata
where soundex(names) = soundex('question')
order by case when INSTR('question', names) = 1 THEN 0 ELSE 1 END ASC
, LENGTH(names) desc
LIMIT 1




0
 
jason987Author Commented:
soundex only works with English "word" strings though if I am reading right.  What if the string is numeric or just random characters?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>What if the string is numeric or just random characters?
then, indeed, you are "unlucky", at least with MySQL there will be very difficult to find an alternative with the assumptions you posted...
0
 
jason987Author Commented:
Bummer, so I guess I would have to do it programtically.  I'll leave this open for a day or two.

Thanks for you help though.
0
 
jason987Author Commented:
I suggest 1/2 credit for the effort but the solution was not posted.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now