How to search for Soundex () substrings in MySQL?

Question

I have a problem with Joomla! 3 integrated search engine. This engine indexer creates the so-called sound values ​​when indexing content, for example,

TestObject, Testobject 1, Testobject 2239923, Text Object ....

which have the same s23de3 soundex value.

Now my problem is: if I search Test , then there will be no results, since the soundex value for this term is T230.

The query used by the search engine:

 SELECT DISTINCT t.term_id AS id, t.term AS term FROM tablename AS t WHERE t.soundex = SOUNDEX('test') 

I checked the soundex_match function in in this section , but unfortunately this cannot solve my problem because it does not compare soundex values.

I want to avoid hacking the cms core and would like to understand if there is any approximation procedure available for comparing soundex values, for example, for regular queries using the % symbol, which I could try to implement using a plugin or something else.

The MSSQL DIFFERENCE function mentioned here would be ideal if it were available in MySQL and was ready to use the soundex value as the second parameter.

I am not very good at MySQL and have no idea how to improve the query in the same way as soundex-substrings.

Answer 1

You probably want to calculate the Levenshtein distance ; but if you just want to find those records that start with something similar to a search query, you can break any trailing 0 (which is just used to fill in), and then look for soundex strings with the resulting prefix:

 WHERE t.soundex LIKE CONCAT(TRIM(TRAILING '0' FROM SOUNDEX('test')), '%')