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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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 ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 :
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