Mysql function to soundex match a word in a multi word string


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:

  • needle: The word you are looking for
  • haysack: The string of words among which you are searching
  • 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.

  • , ,

    1. #1 by Bruno on December 9, 2011 - 10:17 pm

      Excelent!!! Perfect! Great post!!!!!!! Thank you very much!!!!

    2. #2 by Mantonio on December 21, 2012 - 11:36 pm

      Great idea, but this does not find ‘test’ in ‘Testobject’ when using query SELECT soundex_match(‘test’, ‘Testobject’, ‘ ‘);
      How to fund substrings using this function?

    3. #3 by colleges for massage therapy In Ontario on January 30, 2013 - 4:42 am

      Hi! This is my 1st comment here so I just wanted to
      give a quick shout out and say I truly enjoy reading through your blog posts.
      Can you recommend any other blogs/websites/forums that deal with the same subjects?
      Appreciate it!

    4. #4 by Pankaj on February 8, 2013 - 2:28 pm

      Hi i tried to use this function please can u give me one example how to use mysql function in php

    5. #5 by Imran on February 8, 2013 - 3:30 pm

      Hi Pankaj,

      For example you have a varchar type field `haystack` in a table named `search` and you want to get the rows of the table where it has words that sounds like `rose`. Your query would be:

      select * from search where soundex_match(‘rose’, search, ‘ ‘);

      In php you can just put your sql query in mysql_query() function and get the results as normal query.

    6. #6 by runescape money on April 10, 2013 - 10:04 am

      Do not ignore any ofThe little details

    7. #7 by Md Mainuddin on December 18, 2015 - 8:43 am

      Dear Imran,

      Thank you for your great work on the soundex match.
      I am having a issue with your function if you could help me.
      I have product name like “BIC Clic Stic Ice with Rubber Grip” and I want to select it using “Bic Click Stick”.

      When I search using “Click” or “Stick” your function works great but I need to make it work with keyword “Bic Click Stick” it does not return anything. If you could help me getting a solution that would be a great help for me.

      All the best,
      Mainuddin

    8. #8 by Carlos on March 18, 2016 - 2:09 am

      Thanks man,

      You really saved my day! :)

      Md Mainuddin, try a more complex search using something like this:

      SELECT
      *
      FROM
      `table`
      where
      soundex_match(‘Bic Click Stick’, product, ‘ ‘) OR
      SOUNDEX(`product`) LIKE CONCAT(
      TRIM(TRAILING ‘0’ FROM SOUNDEX(‘Bic Click Stick’)),
      ‘%’
      );

    (will not be published)