9GR3TyZN

1Compare String function in mysql by words
2CREATE DEFINER=`root`@`localhost` FUNCTION `CompareStrings`(str1 VARCHAR(255),str2 VARCHAR(255)) RETURNS double
3BEGIN
4DECLARE cur_position INT DEFAULT 1 ; 
5DECLARE remainder TEXT;
6DECLARE cur_string VARCHAR(50);
7DECLARE delimiter_length TINYINT UNSIGNED;
8DECLARE numberMatch INT;
9DECLARE total INT;
10DECLARE result DOUBLE DEFAULT 0;
11DECLARE delim VARCHAR(10);
12DECLARE string2 VARCHAR(255);
13SET delim = ' ';
14
15DROP TEMPORARY TABLE IF EXISTS SplitString1;
16CREATE TEMPORARY TABLE SplitString1 (
17    SplitString1ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
18    val VARCHAR(50) NOT NULL
19) ENGINE=MyISAM;
20DROP TEMPORARY TABLE IF EXISTS SplitString2;
21CREATE TEMPORARY TABLE SplitString2 (
22    SplitString1ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
23    val VARCHAR(50) NOT NULL
24) ENGINE=MyISAM;
25
26SET remainder = str1;
27SET delimiter_length = CHAR_LENGTH(delim);
28
29WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
30    SET cur_position = INSTR(remainder, delim);
31    IF cur_position = 0 THEN
32        SET cur_string = remainder;
33
34    ELSE
35        SET cur_string = LEFT(remainder, cur_position - 1);
36    END IF;
37    IF TRIM(cur_string) != '' THEN
38        INSERT INTO SplitString1(val) VALUES (cur_string);
39    END IF;
40    SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);
41END WHILE;
42SET remainder = str2;
43SET cur_position = 1;
44WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
45    SET cur_position = INSTR(remainder, delim);
46    IF cur_position = 0 THEN
47        SET cur_string = remainder;
48
49    ELSE
50        SET cur_string = LEFT(remainder, cur_position - 1);
51    END IF;
52    IF TRIM(cur_string) != '' THEN
53        INSERT INTO SplitString2(val) VALUES (cur_string);
54    END IF;
55    SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);
56END WHILE;
57SELECT count(*) INTO numberMatch 
58FROM SplitString1 s1 JOIN SplitString2 s2 ON s1.val = s2.val;
59RETURN result;
60END
61	
62RETURN result;
63	
64RETURN numberMatch;
65	
66CREATE DEFINER=`root`@`localhost` FUNCTION `CompareStrings`(str1 VARCHAR(255),str2 VARCHAR(255)) RETURNS double
67	
68CREATE DEFINER=`root`@`localhost` FUNCTION `CompareStrings`(str1 VARCHAR(255),str2 VARCHAR(255)) RETURNS double READS SQL DATA