Advertisement
Guest User

MySQL Damerau-Levenshtein

a guest
Aug 17th, 2010
2,186
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.41 KB | None | 0 0
  1. CREATE DEFINER=`root`@`localhost` FUNCTION `DAMLEV`(`s1` VARCHAR(255), `s2` VARCHAR(255)) RETURNS int(11)
  2.     DETERMINISTIC
  3.     SQL SECURITY INVOKER
  4. BEGIN
  5.   DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
  6. DECLARE s1_char CHAR;
  7. DECLARE cv0, cv1 VARBINARY(256);
  8. SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
  9. IF s1 = s2 THEN
  10.     RETURN 0;
  11. ELSEIF s1_len = 0 THEN
  12.     RETURN s2_len;
  13. ELSEIF s2_len = 0 THEN
  14.     RETURN s1_len;
  15. ELSE
  16.     WHILE j <= s2_len DO
  17.       SET cv1 = CONCAT(cv1, UNHEX(HEX(j)));
  18. SET j = j + 1;
  19. END WHILE;
  20. WHILE i <= s1_len DO
  21.       SET s1_char = SUBSTRING(s1, i, 1);
  22. SET c = i;
  23. SET cv0 = UNHEX(HEX(i));
  24. SET j = 1;
  25. WHILE j <= s2_len DO
  26.           SET c = c + 1;
  27. IF s1_char = SUBSTRING(s2, j, 1) THEN
  28.                 SET cost = 0;
  29. ELSE
  30.                 SET cost = 1;
  31. END IF;
  32. SET c_temp = CONV (HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
  33. IF c > c_temp THEN
  34.                 SET c = c_temp;
  35. END IF;
  36. SET c_temp = CONV (HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
  37. IF c > c_temp THEN
  38.                  SET c = c_temp;
  39. END IF;
  40. IF i > 1 AND j > 1 AND (s1_char = SUBSTRING (s2, j - 1, 1)) AND (SUBSTRING (s1, i - 1, 1) = SUBSTRING(s2, j, 1)) THEN
  41.                         SET c_temp = CONV(HEX(SUBSTRING(cv1, j + 1, 1)), 16, 10);
  42. END IF;
  43. IF c > c_temp THEN
  44.                         SET c = c_temp;
  45. END IF;
  46. SET cv0 = CONCAT(cv0, UNHEX(HEX(c)));
  47. SET j = j + 1;
  48. END WHILE;
  49. SET cv1 = cv0;
  50. SET i = i + 1;
  51. END WHILE;
  52. END IF;
  53. RETURN c;
  54.  
  55. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement