imranul hoque's blog

soundex is a very useful mysql function when we try to compare 2 words if they sounds similar. Here is the official manual for the function. Soundex is pretty easy to use when we are comparing 2 words. For example, check the following query:

 select if(soundex('Rösle')= soundex('rosle'), 1, 0); 

It’ll return 1 as both the words sounds same. But what if you are asked to check if a whole paragraph has any word that sounds like, say “Rösle”? Here is my simplest mysql function that’ll do the work for you.

drop function if exists soundex_match;
delimiter $$
create function soundex_match (needle varchar(128), haystack text, splitChar varchar(1)) returns tinyint
  deterministic
  begin
    declare spacePos int;
    declare searchLen int default length(haystack);
    declare curWord varchar(128) default '';
    declare tempStr text default haystack;
    declare tmp text default '';
    declare soundx1 varchar(64) default soundex(needle);
    declare soundx2 varchar(64) default '';

    set spacePos = locate(splitChar, tempStr);

    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;
      end if;

      if spacePos != 0 then
        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
$$
delimiter ;

The function takes 3 arguments:

splitChar: The whitespace charater that’ll split the string into single words. Generally it is the space(‘ ‘)

If any word in haystack sounds similar to needle, the function will return 1 and 0 otherwise.