2

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.

Aqeel Ashiq
  • 1,988
  • 5
  • 24
  • 57
  • Can you describe what you mean with "difference", exactly? – Aioros Sep 04 '13 at 08:08
  • @Aioros Like the difference is implemented in MSSQL server. – Aqeel Ashiq Sep 04 '13 at 08:10
  • According to the docs, DIFFERENCE returns the number of characters that are the same in the SOUNDEXes of two strings. This is going to be pretty complicated to realize in a MySQL query. You're probably better off writing a stored procedure or managing it in your programming language. – Aioros Sep 04 '13 at 08:21

1 Answers1

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)
skv
  • 1,793
  • 3
  • 19
  • 27
  • Thanks a lot for the idea as it gave me direction. One more question for this: How many times will the soundex function be called in your query? It optimistically assume that it should be called only once for same input? [Question Link](http://stackoverflow.com/questions/18616924/does-mysql-query-cache-the-dynamically-calculated-columns) – Aqeel Ashiq Sep 05 '13 at 05:59
  • I just replied your other query, but just to point at the solution, the answer is yes mysql calls it 4 times and to avoid it, you make a subquery, for detailed answer see the question linked above – skv Sep 05 '13 at 06:29