Membuat Fungsi Similar Text di Mysql

Kemarin saya coba browsing, karena saya sedang membutuhkan fungsi pencarian yang serupa untuk query sql saya dengan mysql.

Jadi misalkan saya punya table dengan kolom nama, nah biasanya saya hanya menggunakan fungsi LIKE untuk mencari nama di dalam kolom tersebut, setelah baca-baca ternyata ada fungsi yang lebih keren, dia bisa melakukan pencarian dengan kata yang hampir mirip, kyak google sugest gitu.

Bagi yang pengen nyobain ini fungsinya, bisa di compile langsung dari sql phpmyadmin.

DELIMITER $$

CREATE FUNCTION `levenshtein_ratio`( s1 text, s2 text ) RETURNS int(11)
    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$$
DELIMITER ;

DELIMITER $$

CREATE FUNCTION `levenshtein_ratio`( s1 text, s2 text ) RETURNS int(11)

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$$

DELIMITER ;

DELIMITER $$

CREATE FUNCTION `levenshtein`( s1 text, s2 text) RETURNS int(11)
    DETERMINISTIC
BEGIN 
    DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT; 
    DECLARE s1_char CHAR; 
    DECLARE cv0, cv1 text; 
    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 ;

DELIMITER $$

CREATE FUNCTION `levenshtein`( s1 text, s2 text) RETURNS int(11)

DETERMINISTIC

BEGIN

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

DECLARE s1_char CHAR;

DECLARE cv0, cv1 text;

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 ;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `jaro_winkler_similarity`(
in1 varchar(255),
in2 varchar(255)
) RETURNS float
DETERMINISTIC
BEGIN
#finestra:= search window, curString:= scanning cursor for the original string, curSub:= scanning cursor for the compared string
declare finestra, curString, curSub, maxSub, trasposizioni, prefixlen, maxPrefix int;
declare char1, char2 char(1);
declare common1, common2, old1, old2 varchar(255);
declare trovato boolean;
declare returnValue, jaro float;
set maxPrefix=6; #from the original jaro - winkler algorithm
set common1="";
set common2="";
set finestra=(length(in1)+length(in2)-abs(length(in1)-length(in2))) DIV 4
+ ((length(in1)+length(in2)-abs(length(in1)-length(in2)))/2) mod 2;
set old1=in1;
set old2=in2;

#calculating common letters vectors
set curString=1;
while curString<=length(in1) and (curString<=(length(in2)+finestra)) do
set curSub=curstring-finestra;
if (curSub)<1 then
set curSub=1;
end if;
set maxSub=curstring+finestra;
if (maxSub)>length(in2) then
set maxSub=length(in2);
end if;
set trovato = false;
while curSub<=maxSub and trovato=false do
if substr(in1,curString,1)=substr(in2,curSub,1) then
set common1 = concat(common1,substr(in1,curString,1));
set in2 = concat(substr(in2,1,curSub-1),concat("0",substr(in2,curSub+1,length(in2)-curSub+1)));
set trovato=true;
end if;
set curSub=curSub+1;
end while;
set curString=curString+1;
end while;
#back to the original string
set in2=old2;
set curString=1;
while curString<=length(in2) and (curString<=(length(in1)+finestra)) do
set curSub=curstring-finestra;
if (curSub)<1 then
set curSub=1;
end if;
set maxSub=curstring+finestra;
if (maxSub)>length(in1) then
set maxSub=length(in1);
end if;
set trovato = false;
while curSub<=maxSub and trovato=false do
if substr(in2,curString,1)=substr(in1,curSub,1) then
set common2 = concat(common2,substr(in2,curString,1));
set in1 = concat(substr(in1,1,curSub-1),concat("0",substr(in1,curSub+1,length(in1)-curSub+1)));
set trovato=true;
end if;
set curSub=curSub+1;
end while;
set curString=curString+1;
end while;
#back to the original string
set in1=old1;

#calculating jaro metric
if length(common1)<>length(common2)
then set jaro=0;
elseif length(common1)=0 or length(common2)=0
then set jaro=0;
else
#calcolo la distanza di winkler
#passo 1: calcolo le trasposizioni
set trasposizioni=0;
set curString=1;
while curString<=length(common1) do
if(substr(common1,curString,1)<>substr(common2,curString,1)) then
set trasposizioni=trasposizioni+1;
end if;
set curString=curString+1;
end while;
set jaro=
(
length(common1)/length(in1)+
length(common2)/length(in2)+
(length(common1)-trasposizioni/2)/length(common1)
)/3;

end if; #end if for jaro metric

#calculating common prefix for winkler metric
set prefixlen=0;
while (substring(in1,prefixlen+1,1)=substring(in2,prefixlen+1,1)) and (prefixlen<6) do
set prefixlen= prefixlen+1;
end while;


#calculate jaro-winkler metric
return jaro+(prefixlen*0.1*(1-jaro));
END

100

101

102

103

104

105

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `jaro_winkler_similarity`(

in1 varchar(255),

in2 varchar(255)

) RETURNS float

DETERMINISTIC

BEGIN

#finestra:= search window, curString:= scanning cursor for the original string, curSub:= scanning cursor for the compared string

declare finestra, curString, curSub, maxSub, trasposizioni, prefixlen, maxPrefix int;

declare char1, char2 char(1);

declare common1, common2, old1, old2 varchar(255);

declare trovato boolean;

declare returnValue, jaro float;

set maxPrefix=6; #from the original jaro - winkler algorithm

set common1="";

set common2="";

set finestra=(length(in1)+length(in2)-abs(length(in1)-length(in2))) DIV 4

+ ((length(in1)+length(in2)-abs(length(in1)-length(in2)))/2) mod 2;

set old1=in1;

set old2=in2;

#calculating common letters vectors

set curString=1;

while curString<=length(in1) and (curString<=(length(in2)+finestra)) do

set curSub=curstring-finestra;

if (curSub)<1 then

set curSub=1;

end if;

set maxSub=curstring+finestra;

if (maxSub)>length(in2) then

set maxSub=length(in2);

end if;

set trovato = false;

while curSub<=maxSub and trovato=false do

if substr(in1,curString,1)=substr(in2,curSub,1) then

set common1 = concat(common1,substr(in1,curString,1));

set in2 = concat(substr(in2,1,curSub-1),concat("0",substr(in2,curSub+1,length(in2)-curSub+1)));

set trovato=true;

end if;

set curSub=curSub+1;

end while;

set curString=curString+1;

end while;

#back to the original string

set in2=old2;

set curString=1;

while curString<=length(in2) and (curString<=(length(in1)+finestra)) do

set curSub=curstring-finestra;

if (curSub)<1 then

set curSub=1;

end if;

set maxSub=curstring+finestra;

if (maxSub)>length(in1) then

set maxSub=length(in1);

end if;

set trovato = false;

while curSub<=maxSub and trovato=false do

if substr(in2,curString,1)=substr(in1,curSub,1) then

set common2 = concat(common2,substr(in2,curString,1));

set in1 = concat(substr(in1,1,curSub-1),concat("0",substr(in1,curSub+1,length(in1)-curSub+1)));

set trovato=true;

end if;

set curSub=curSub+1;

end while;

set curString=curString+1;

end while;

#back to the original string

set in1=old1;

#calculating jaro metric

if length(common1)<>length(common2)

then set jaro=0;

elseif length(common1)=0 or length(common2)=0

then set jaro=0;

else

#calcolo la distanza di winkler

#passo 1: calcolo le trasposizioni

set trasposizioni=0;

set curString=1;

while curString<=length(common1) do

if(substr(common1,curString,1)<>substr(common2,curString,1)) then

set trasposizioni=trasposizioni+1;

end if;

set curString=curString+1;

end while;

set jaro=

(

length(common1)/length(in1)+

length(common2)/length(in2)+

(length(common1)-trasposizioni/2)/length(common1)

)/3;

end if; #end if for jaro metric

#calculating common prefix for winkler metric

set prefixlen=0;

while (substring(in1,prefixlen+1,1)=substring(in2,prefixlen+1,1)) and (prefixlen<6) do

set prefixlen= prefixlen+1;

end while;

#calculate jaro-winkler metric

return jaro+(prefixlen*0.1*(1-jaro));

END

itu tiga fungsi yang saya coba, untuk penggunaannya tinggal panggil fungsinya saat melakukanquery :

SELECT *, jaro_winkler_similarity(nama, "nama yg dicari") AS score
FROM table_nama ORDER BY score DESC

1 2	SELECT *, jaro_winkler_similarity(nama, "nama yg dicari") AS score FROM table_nama ORDER BY score DESC

cukup ganti aja nama fungsinya, klo mau coba fungsi yang lain, thanks

Was this article helpful?

Leave a Reply Cancel reply

Our Office Address

Phone

Email

Senin - Jum'at