字符串相似度处理函数
oracle里面查比如存储过程里面与表SALES有关jobs:
SELECT * FROM (SELECT a.name,upper(b.what)AS what,SYS.UTL_MATCH.edit_distance_similarity(a.name,upper(b.what)) AS similarity FROM dba_source a,dba_jobs b
WHERE SYS.UTL_MATCH.edit_distance_similarity(a.name,upper(b.what))
>80AND upper(a.text) LIKE
"%SALES%"AND b.what NOT LIKE
"%dbms_refresh%")ORDER BY
3 DESC;
有自带相似度函数 SYS.UTL_MATCH.edit_distance_similarity 可以直接用。
mysql8里面我只查到了一个搜索相关的文档文档地址并不适合我自己用,找了一下大佬的文章,发现了一个能用的自定义函数,用于计算字符串相似度。
DELIMITER $$CREATE DEFINER
=`root`@`localhost` FUNCTION `COMPARE_STRING`( 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 THENRETURN
0;ELSEIF s1_len
= 0 THENRETURN s2_len;
ELSEIF s2_len
= 0 THENRETURN s1_len;
ELSE
WHILE j
<= s2_len DOSET cv1
= CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;END WHILE;
WHILE i
<= s1_len DOSET s1_char
= SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;WHILE j
<= s2_len DOSET c
= c + 1;IF s1_char
= SUBSTRING(s2, j, 1) THENSET 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 THENSET 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 `SIMILARITY_STRING`(a text, b text) RETURNS doubleBEGIN
RETURN ABS(((COMPARE_STRING(a, b)
/ length(b)) * 100) - 100);END$$
DELIMITER ;
试了一下还挺好用的,一些逻辑可以自己再适当的修改。
而在gp里面,我找了许久,发现一个 fuzzystrmatch 看起来比较高档的函数,这个函数需要安装:
[gpadmin@SZWPLDB1085 ~]$ psql -d postgres -f $GPHOME/share/postgresql/contrib/fuzzystrmatch.sqlSET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
然后会在 postgres 库多出一些函数:
用法在文档地址
以上是 字符串相似度处理函数 的全部内容, 来源链接: utcz.com/z/533438.html