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

Posted on 2007-09-28
Last Modified: 2008-09-08
I have a table called names with one field 'names'


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.

Question by:jason987
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    did you already notice the function SOUNDEX in mysql:

    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

    LVL 5

    Accepted Solution

    soundex only works with English "word" strings though if I am reading right.  What if the string is numeric or just random characters?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >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...
    LVL 5

    Author Comment

    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.
    LVL 5

    Author Comment

    I suggest 1/2 credit for the effort but the solution was not posted.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now