MySQL Forums
Forum List  »  Stored Procedures

Re: Help needed for migrating an Oracle UDF to Mysql UDF
Posted by: Praveen P
Date: November 05, 2016 11:52AM

Thanks Mr.peter Brawley. Below is the MysQL version function which I created. But it is not working properly. It is returning score as 100 even if the strings doesn't match. I am struggling to identify the problem and fix it. Will be grateful if you can help me on this.

Quote


DROP FUNCTION Fn_Jws;

CREATE FUNCTION Fn_Jws (p_string1 VARCHAR(300), p_string2 VARCHAR(300)) RETURNS float
DETERMINISTIC
CONTAINS SQL
BEGIN

DECLARE v_string1 VARCHAR (1000);
DECLARE v_string2 VARCHAR (1000);
DECLARE v_closeness int default 0;
DECLARE v_temp VARCHAR (1000);
DECLARE v_comp1 VARCHAR (1000);
DECLARE v_comp2 VARCHAR (1000);
DECLARE v_matches int default 0;
DECLARE v_char VARCHAR(1);
DECLARE v_transpositions int default 0;
DECLARE v_d_jaro int default 0;
DECLARE v_leading int default 0;
DECLARE v_d_winkler int default 0;
DECLARE v_jws, i int default 0;

IF p_string1 IS NULL OR p_string2 IS NULL THEN
RETURN 0;
END IF;

SET v_string1 = fn_remove_accents(p_string1);
SET v_string2 = fn_remove_accents(p_string2);

SET v_closeness = (greatest(length(v_string1), length(v_string2)) / 2) - 1;
-- find matching characters and transpositions within closeness:
SET @v_temp = v_string2;
SET @vLength = length(v_string1);
WHILE i < @vLength DO
SET i = i + 1;

IF (instr(@v_temp, substr(v_string1, i, 1))) > 0 THEN

SET v_char = substr(v_string1, i, 1);

IF abs(instr(v_string1, v_char) - instr(v_string2, v_char)) <= v_closeness THEN
SET v_comp1 = concat(IFNULL(v_comp1, ''), substr(v_string1, i, 1));
SET @v_temp = concat(substr(@v_temp, 1, instr(@v_temp, substr(v_string1, i, 1)) - 1),
substr(@v_temp, instr(@v_temp, substr(v_string1, i, 1)) + 1));
END IF;

END IF;

END WHILE;

SET @v_temp = v_string1;
SET i = 0;
SET @vLength = length(v_string2);
WHILE i < @vLength DO
SET i = i + 1;

IF (instr(@v_temp, substr(v_string2, i, 1))) > 0 THEN
SET v_char = substr(v_string2, i, 1);
IF abs(instr(v_string2, v_char) - instr(v_string1, v_char)) <= v_closeness THEN
SET v_comp2 = concat(IFNULL(v_comp2, ''), substr(v_string2, i, 1));
SET @v_temp = concat(substr(@v_temp, 1, instr(@v_temp, substr(v_string2, i, 1)) - 1),
substr(@v_temp, instr(@v_temp, substr(v_string2, i, 1)) + 1));
END IF;

END IF;

END WHILE;

IF v_comp1 IS NULL OR v_comp2 IS NULL THEN
return 0;
END IF;

SET I = 0;
SET @vLength = least(length(v_comp1), length(v_comp2));
WHILE i < @vLength DO
SET i = i + 1;

IF substr(v_comp1, i, 1) = substr(v_comp2, i, 1) THEN
SET v_matches = v_matches + 1;
ELSE
SET v_char = substr(v_comp1, i, 1);
IF abs(instr(v_string1, v_char) - instr(v_string2, v_char)) <= v_closeness THEN
SET v_transpositions = v_transpositions + 1;
SET v_matches = v_matches + 1;
END IF;
END IF;

END WHILE;

SET v_transpositions = v_transpositions / 2;

IF v_matches = 0 THEN
RETURN 0;
END IF;

SET v_d_jaro = ((v_matches / length(v_string1)) +
(v_matches / length(v_string2)) +
((v_matches - v_transpositions) / v_matches))
/ 3;

SET i = 0;
-- count matching leading characters (up to 4):
SET @vLength = least(length(v_string1), length(v_string2), 4);

LOOP1: WHILE i < @vLength DO
SET i = i + 1;

IF substr(v_string1, i, 1) = substr(v_string2, i, 1) THEN
SET v_leading = v_leading + 1;
ELSE
-- LEAVE LOOP1;
SET v_leading = v_leading;
END IF;

END WHILE;

SET v_d_winkler = v_d_jaro + ((v_leading * .1) * (1 - v_d_jaro));
SET v_jws = round(v_d_winkler * 100);

RETURN v_jws;

END;



Regards
Praveen

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.