Mysql Optimized Levenshtein

Question

I'm trying to create an optimized levenshtein function in mysql. I can't find my error, my console returns me this:

"#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 
 '@differences, @lenRight, @lenLeft, @leftIndex, @rightIndex, @compareLength INT;' 
 at line 5"

Code:

/****** Script Date: 06/10/2009 09:36:44 ******/ 

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `optmizedlevenshtein`( s1 VARCHAR(255), s2 VARCHAR(255) ) RETURNS int(11)
    DETERMINISTIC 
BEGIN
   DECLARE @differences, @lenRight, @lenLeft, @leftIndex, @rightIndex, @compareLength INT;  
   DECLARE @left_char, @right_char CHAR(1);

   SET @lenLeft = LEN(s1);
   SET @lenRight = LEN(s2);
   SET @differences = 0;
  
   If @lenLeft = 0 
   BEGIN
      SET @differences = @lenRight GOTO done; 
   END
   If @lenRight = 0 
   BEGIN
      SET @differences = @lenLeft;
      GOTO done;
   END 
   GOTO comparison;  
 
   comparison: 
   IF (@lenLeft >= @lenRight) 
      SET @compareLength = @lenLeft; 
   Else
      SET @compareLength = @lenRight;  
   SET @rightIndex = 1;
   SET @leftIndex = 1;
   WHILE @leftIndex <= @compareLength 
   BEGIN
      SET @left_char = substring(s1, @leftIndex, 1);
      SET @right_char = substring(s2, @rightIndex, 1);
      IF  @left_char <> @right_char 
      BEGIN -- Would an insertion make them re-align? 
         IF(@left_char = substring(s2, @rightIndex+1, 1))    
            SET @rightIndex = @rightIndex + 1;
         -- Would an deletion make them re-align? 
         ELSE
            IF(substring(s1, @leftIndex+1, 1) = @right_char)
               SET @leftIndex = @leftIndex + 1;
               SET @differences = @differences + 1; 
      END
      SET @leftIndex = @leftIndex + 1;
      SET @rightIndex = @rightIndex + 1; 
   END 
   GOTO done;  
 
   done: 
      RETURN @differences;
END$$

DELIMITER ;

Answer 1

First off, this doesn't appear to be MySQL at all. There's no GOTO in MySQL.

You don't need the @ before the variable names in functions/stored procedures. You can use:

DECLARE differences, lenRight, lenLeft, leftIndex, rightIndex, compareLength INT;  
DECLARE left_char, right_char CHAR(1);

Anyway, here's something you could use right out of the box: How to add levenshtein function in mysql?

Good luck!