MySQL Fuzzy Text Searching Using the SOUNDEX Function
-- ======================================================================
-- MySQL Fuzzy Text Searching Using the SOUNDEX Function
--
-- The Soundex() function is only one of several ways to perform fuzzy
-- searches on your MySQL data.
--
-- http://www.databasejournal.com/features/mysql/mysql-fuzzy-text-searching-using-the-soundex-function.html
-- ======================================================================
-- ----------------------------------------------------------------------
-- soundex_match
--
-- Soundex match a word in a multi-word string
--
-- @needle......: The word you are looking for.
-- @haystack....: The block of text that you are searching.
-- @splitChar...: The whitespace character that'll split the
-- string into single words. Generally it is
-- the space(' ').
--
-- If any word in the haystack sounds similar to the needle,
-- the function will return 1. Otherwise it returns 0.
-- ----------------------------------------------------------------------
drop function if exists soundex_match;
create function soundex_match(
needle varchar(128),
haystack text,
splitChar varchar(1)
)
returns tinyint(4)
deterministic
begin
declare spacePos int;
declare searchLen int default 0;
declare tempStr text default haystack;
declare tmp text default '';
declare soundx1 varchar(64) default '';
declare soundx2 varchar(64) default '';
set searchLen = length(haystack);
set spacePos = locate(splitChar, tempStr);
set soundx1 = soundex(needle);
while searchLen > 0 do
if spacePos = 0
then
set tmp = tempStr;
select soundex(tmp)
into soundx2;
if soundx1 = soundx2
then
return 1;
else
return 0;
end if;
else
set tmp = substr(tempStr, 1, spacePos - 1);
set soundx2 = soundex(tmp);
if soundx1 = soundx2
then
return 1;
end if;
set tempStr = substr(tempStr, spacePos + 1);
set searchLen = length(tempStr);
end if;
set spacePos = locate(splitChar, tempStr);
end while;
return 0;
end;
-- ----------------------------------------------------------------------
-- soundex_match_all
--
-- Soundex match multiple terms against multiple words
--
-- @needle......: The word you are looking for.
-- @haystack....: The block of text that you are searching.
-- @splitChar...: The whitespace character that'll split the
-- string into single words. Generally it is
-- the space(' ').
--
-- If any word in the haystack sounds similar to the needle,
-- the function will return 1. Otherwise it returns 0.
-- ----------------------------------------------------------------------
drop function if exists soundex_match_all;
create function soundex_match_all(
needle varchar(128),
haystack text,
splitChar varchar(1)
)
returns tinyint(4)
deterministic
begin
/* find the first instance of the splitting character */
declare comma int default 0;
declare word text;
set comma = LOCATE(splitChar, needle);
set word = TRIM(needle);
if LENGTH(haystack) = 0
then
return 0;
elseif comma = 0
then
/* one word search term */
return soundex_match(word, haystack, splitChar);
end if;
set word = trim(substr(needle, 1, comma));
/* Insert each split variable into the word variable */
repeat
if soundex_match(word, haystack, splitChar) = 0
then
return 0;
end if;
/* get the next word */
set needle = trim(substr(needle, comma));
set comma = LOCATE(splitChar, needle);
if comma = 0
then
/* last word */
return soundex_match(needle, haystack, splitChar);
end if;
set word = trim(substr(needle, 1, comma));
until length(word) = 0
end repeat;
return 0;
end;