How to add levenshtein function in mysql?

T

4

34

I got the code for Levenshtein distance for MySQL from http://kristiannissen.wordpress.com/2010/07/08/mysql-levenshtein/^{(archive.org link)}, but how to add that function in MySQL? I am using XAMPP and I need it for search in PHP.

Telford answered 17/12, 2012 at 7:19 Comment(1)

Earlier question with apparently the same code asking why it throws errors: https://mcmap.net/q/358077/-mysql-levenshtein – Tehuantepec 31/8, 2014 at 20:7

L

17

I have connected to my MySQL server and simply executed this statement in MySQL Workbench, and it simply worked - I now have new function levenshtein().

For example, this works as expected:

SELECT levenshtein('abcde', 'abced')

2

Lenient answered 17/12, 2012 at 7:34 Comment(5)

I have run the statement in sql section of xampp(phpmyadmin), but an error occured:: SQL query: CREATE FUNCTION levenshtein( s1 VARCHAR( 255 ) , s2 VARCHAR( 255 ) ) RETURNS INT DETERMINISTIC BEGIN DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT; MySQL said: #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 5 – Telford 17/12, 2012 at 8:51

What is your server version? Mine is 5.5.28, but I think it should work in 5.1 and onwards. Can you install MySQL Workbench (see link above or google it) and try executing this SQL from workbench? – Lenient 17/12, 2012 at 9:1

I have run the code on mysql workbench and got the following error. Is there anything wrong on my code(provided above)? 0 13 12:15:59 CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) ) RETURNS INT DETERMINISTIC BEGIN DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT Error Code: 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 5 0.000 sec – Telford 18/12, 2012 at 6:37

It's very time costly to execute the levenshtein function in mysql. Please have a look at this document where I have explained my probelm in detail - 1drv.ms/w/s!AjDzbQws1k6_gudovwP9ouhy-mOqpQ Can you please give me some solution for this? Thanks in advance. – Empoverish 13/9, 2016 at 2:45

Here is correct function : gist.github.com/Kovah/df90d336478a47d869b9683766cff718 – Goddess 5/6, 2018 at 7:40

D

4

DELIMITER $$
CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) ) 
RETURNS INT 
DETERMINISTIC 
BEGIN 
DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT; 
DECLARE s1_char CHAR; 
-- max strlen=255 
DECLARE cv0, cv1 VARBINARY(256); 
SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0; 
IF s1 = s2 THEN 
  RETURN 0; 
ELSEIF s1_len = 0 THEN 
  RETURN s2_len; 
ELSEIF s2_len = 0 THEN 
  RETURN s1_len; 
ELSE 
  WHILE j <= s2_len DO 
    SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1; 
  END WHILE; 
  WHILE i <= s1_len DO 
    SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1; 
    WHILE j <= s2_len DO 
      SET c = c + 1; 
      IF s1_char = SUBSTRING(s2, j, 1) THEN  
        SET cost = 0; ELSE SET cost = 1; 
      END IF; 
      SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost; 
      IF c > c_temp THEN SET c = c_temp; END IF; 
        SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1; 
        IF c > c_temp THEN  
          SET c = c_temp;  
        END IF; 
        SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1; 
    END WHILE; 
    SET cv1 = cv0, i = i + 1; 
  END WHILE; 
END IF; 
RETURN c; 
END$$
DELIMITER ;

Dickenson answered 28/7, 2018 at 10:49 Comment(2)

This doesn't render a result for me, there are no syntax errors or runtime errors, just an "OK" in the console output and no result set. – Gollin 16/1, 2019 at 0:38

@JonathanNeufeld, This defines the levenshtein function, as the question asks. You'll need to call the function to get a result. – Ecclesiastical 8/7, 2019 at 14:41

G

2

the web paged linked in question is dead. By the way, the function's create code is here:

DELIMITER $$

CREATE FUNCTION levenshtein( s1 varchar(255), s2 varchar(255) ) returns int deterministic 
BEGIN 
  DECLARE s1_len, 
    s2_len, 
    i, 
    j, 
    c, 
    c_temp, 
    cost          int; 
  declare s1_char char; 
  -- max strlen=255 
  declare cv0, 
    cv1 varbinary(256); 
  set s1_len = char_length(s1), 
    s2_len = char_length(s2), 
    cv1 = 0x00, 
    j = 1, 
    i = 1, 
    c = 0; 
  if s1 = s2 THEN 
  RETURN 0; 
elseif s1_len = 0 THEN 
  RETURN s2_len; 
elseif s2_len = 0 THEN 
  RETURN s1_len; 
  else 
  WHILE j <= s2_len do 
  SET cv1 = concat(cv1, unhex(hex(j))), 
    j = j + 1; 
endWHILE;WHILE i <= s1_len do 
SET s1_char = substring(s1, i, 1), 
  c = i, 
  cv0 = unhex(hex(i)), 
  j = 1;WHILE j <= s2_len do 
SET c = c + 1;IF s1_char = Substring(s2, j, 1) then 
SET cost = 0; 
else 
SET cost = 1;ENDIF;SET c_temp = conv(hex(substring(cv1, j, 1)), 16, 10) + cost;IF c > c_temp then
SET c = c_temp;ENDIF;SET c_temp = conv(hex(substring(cv1, j+1, 1)), 16, 10) + 1;IF c > c_temp then
SET c = c_temp;ENDIF;SET cv0 = concat(cv0, unhex(hex(c))), 
  j = j + 1;ENDWHILE;SET cv1 = cv0, 
  i = i + 1;ENDWHILE;ENDIF;RETURN c;
END;

DELIMITER ;

I hope it helps some body!

Gonagle answered 17/3, 2018 at 9:46 Comment(2)

Sorry, not works to me. check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 9 – Acquainted 19/3, 2018 at 23:35

@MárcioRossato just put DELIMITER $$ before the function – Verse 30/3, 2018 at 5:51

R

1

Login to mysql database through phpmyadmin or through terminal.
click sql.
add delimiter $$ to your the function in the above link and at the end delimiter ;.
once if it successfully compiled you can use it in that database wherever you want like inside of another procedures or functions or queries etc..!

Realpolitik answered 17/12, 2012 at 7:34 Comment(3)

I have run the statement in sql section of xampp(phpmyadmin), but an error occured:: SQL query: CREATE FUNCTION levenshtein( s1 VARCHAR( 255 ) , s2 VARCHAR( 255 ) ) RETURNS INT DETERMINISTIC BEGIN DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT; MySQL said: #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 5 – Telford 17/12, 2012 at 8:51

have you used delimiter before it..! and use definer also while creating the function..It is better use MySQL workbench like tools to work on procedures like...I executed it through MYsql workbench it is working fine – Realpolitik 17/12, 2012 at 10:33

I have run the code on mysql workbench and got the following error. Is there anything wrong on my code(provided above)? 0 13 12:15:59 CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) ) RETURNS INT DETERMINISTIC BEGIN DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT Error Code: 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 5 0.000 sec – Telford 18/12, 2012 at 6:36

Recommended topics

Hot tags