如何在MySQL中搜索Soundex()子字符串? [英] How to search for Soundex() substrings in MySQL?

问题描述

我遇到了Joomla的问题！ 3个集成的搜索引擎.该引擎的索引器在索引内容(例如

i got a problem with the Joomla! 3 integrated search engine. This engine's indexer creates so called soundex-values when indexing content like, for example

Testobject， 测试对象1 测试对象2239923， Textobject ....

Testobject, Testobject 1, Testobject 2239923, Textobject ....

所有具有相同的T23123的soundex值.

which all have the same soundex-value of T23123.

现在我的问题是，如果我搜索Test，将不会有任何结果，因为该词的soundex值为T230.

Now my problem is, if i do a search for Test, then there won't be any results since the soundex-value for this term is T230.

搜索引擎使用的查询是:

The query used by the search engine is:

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

我在本主题，但是很遗憾，这不能解决我的问题，因为它不比较soundex值.

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

我想避免破坏cms核心，并想了解是否存在某种近似程序可用来比较音素值，例如使用%符号时的常规查询，然后我可以尝试使用插件来实现等等.

I want to avoid hacking the cms core and would like understand if there is some kind of approximation procedure available to compare soundex-values like for regular queries when using the % symbol which i could then try to implement using a plugin or whatever.

提到的MSSQL DIFFERENCE函数此处将是理想的选择.

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

我在MySQL方面的经验不是很丰富，也不知道如何改进查询以匹配soundex-substrings.

I am not very well experienced in MySQL and have no idea how to improve the query to also match soundex-substrings.

推荐答案

您可能正在计算 Levenshtein距离；但是，如果您只是想查找以听起来类似于搜索词的开头的记录，则可以剥离任何结尾的0(仅用于填充)，然后搜索带有结果前缀的soundex字符串:

You're probably looking to calculate the Levenshtein distance; but if you simply want to find those records that start with something that sounds similar to the search term, you can strip any trailing 0 (which is merely used for padding) and then search for soundex strings with the resulting prefix:

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

这篇关于如何在MySQL中搜索Soundex()子字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！