Mysql keep getting error while trying to create function

Question

I am trying to create 2 MySQL functions I found here, I will show the shorter one:

CREATE FUNCTION levenshtein_ratio( s1 VARCHAR(255), s2 VARCHAR(255) ) 
  RETURNS INT 
  DETERMINISTIC 
  BEGIN 
    DECLARE s1_len, s2_len, max_len INT; 
    SET s1_len = LENGTH(s1), s2_len = LENGTH(s2); 
    IF s1_len > s2_len THEN  
      SET max_len = s1_len;  
    ELSE  
      SET max_len = s2_len;  
    END IF; 
    RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100); 
  END;

They both keep giving me the same error:

#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

Line 5 on the other function looks like this:

DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;

I looked up error 1064 and found this:

Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR)

Message: %s near '%s' at line %d

I don't know what that means.

I can't figure out what is triggering the error?

Joe Stefanelli · Accepted Answer · 2011-07-19 14:53:30Z

3

Because you are using semi-colons within the body of the function, you need to specify a different delimiter for the creation of the function.

delimiter //
CREATE FUNCTION levenshtein_ratio( s1 VARCHAR(255), s2 VARCHAR(255) ) 
  RETURNS INT 
  DETERMINISTIC 
  BEGIN 
    DECLARE s1_len, s2_len, max_len INT; 
    SET s1_len = LENGTH(s1), s2_len = LENGTH(s2); 
    IF s1_len > s2_len THEN  
      SET max_len = s1_len;  
    ELSE  
      SET max_len = s2_len;  
    END IF; 
    RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100); 
  END//

answered Jul 19, 2011 at 14:53

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kohjah Breese Over a year ago

See here for the same issue with the levenshtein function: openquery.com.au/blog/levenshtein-mysql-stored-function

Collectives™ on Stack Overflow

Mysql keep getting error while trying to create function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related