Update: Changed 0x00 to '\0' as per Jan-Hendrik's comment below.

There are a number of MySQL functions for calculating Levenshtein distance floating around StackOverflow and other forums. They all seem to be based off http://codejanitor.com/wp/2007/02/10/levenshtein-distance-as-a-mysql-stored-function/ (broken link).

Anyway, I couldn't get them to work for me. MySQL complained:

span class="st0">"

Well, it turns out that you need to specify a delimiter instead of the default delimiter of ;. So here's a working version of the levenstein distance function, courtesy of CodeJanitor.

span class="st0">'\0'
  • Gui Daniele

    It works really well. Thanks a lot!

  • Desmond Kyeremeh

    After creating the function i tried to execute

    INSERT INTO notices(about) values(LEVENSHTEIN('DEMON','DESMOND'));

    but received this error
    #1054 – Unknown column '0×00' in 'field list'

  • Vincent Vanhauwaert

    Trrying it in a select statement and have the same error
    Unknown column '0×00' in 'field list'

  • Jan-Hendrik Willms

    For anyone else running into the problem mentioned here:

    Replace 0x00 in the above snippet with the string notation of the null-byte " and it works like a charm.

  • Vincent Vanhauwaert

    Thanks for the hint Jan-Hendrik, now it works like a charm!

  • http://www.couponcrop.com/ Nicholas Z. Cardot

    After trying for hours to find something that worked, I copied and pasted this into a SQL query in phpMyAdmin and it worked like a charm on the first attempt. Thanks so much for making it so incredibly easy.

  • http://www.couponcrop.com/ Nicholas Z. Cardot

    Okay. Now I have a follow up question. Is there a way to make this case insensitive when running the comparison?

  • Carlos Cariello

    MySQL is case insensitive

  • http://www.couponcrop.com/ Nicholas Z. Cardot

    Thanks, mate. I actually figured that out shortly after my initial inquiry. I actually had a different error in my syntax that was throwing it off. Good 'ol echo mysql_error() got me back on the straight narrow.

  • Sonny Kaaswafeltje

    Works perfectly. However on a 3 to 4000 rows table searching for a single word in an indexed varchar(254) column it is horribly slow to generate results. For webapplications absolutely unacceptable.

  • Marlies Olensky

    I'm a newbie… And got this error message when I just copied and pasted this into MySQL phpMyAdmin:
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//' at line 34
    Can anyone help please?

  • ??????? ??????

    Just remove '//' from the end of snippet