Mysql - Calculate soundex difference between two strings

Question

I have some song names and their pre-calculated soundex stored in a mysql table. I want to compare the soundex of user input with the pre-calculated soundex'es. And get the results in ascending order of difference between the user input and song name.

I have tried the following query (in java):

String query="SELECT * FROM song ORDER BY STRCMP(pre_calculated_soundex,SOUNDEX("+user_input+")) ASC ";

But strcmp only returns 1,0 or -1. So ordering is not correct.

Also tried WHERE pre_calculated_soundex=SOUNDEX(user_input), but this just returns exactly matching soundex.

Answer 1

Completely low-tech and assuming that only first four characters of soundex function is being used and also assuming that "aaaa" is the user input

  SELECT * 
FROM   song 
ORDER  BY Substr(pre_calculated_soundex, 1, 1) = 
                    Substr(Soundex("aaaa"), 1, 1) 
                                                 + Substr(pre_calculated_soundex 
                    , 2, 1) = 
                    Substr 
                    (Soundex("aaaa"), 2, 1) 
                    + Substr(pre_calculated_soundex, 3, 1) 
                    = Substr(Soundex("aaaa"), 3, 1) 
                      + Substr(pre_calculated_soundex, 4, 1 
                      ) 
                      = Substr(Soundex("aaaa"), 4, 1)