MySQL LIKE %string% not quite forgiving enough. Anything else I can use?

Question

I've got a client who's asking if their search, which searches for company names which could be searched for in several formats depending on user input, such as the company stored in the database being A J R Kelly Ltd for instance, If a user searches "A J R Kelly" it's found, using;

<cfif pctermsCount gt 0>
AND (LOWER(p.name)  LIKE '%#pcTerms#%')
</cfif>

If they search for "Kelly" the company is found, but if they search for a broken version of the string like "A J Kelly" or "AJ Kelly" it's not found.

Is there anything I can do to make it just a little bit more forgiving?

Thanks.

Answer 1

If you're using MyISAM, you can use full text indexing. See this tutorial

If you're using a different storage engine, you could use a third party full text engine like sphinx, which can act as a storage engine for mysql or a separate server that can be queried.

With MySQL full text indexing a search on A J Kelly would match AJ Kelly (no to confuse matters but A, J and AJ would be ignored as they are too short by default and it would match on Kelly.) Generally Fulltext is much more forgiving (and usually faster than LIKE '%string%') because allows partial matches which can then be ranked on relevance.

You can also use SOUNDEX to make searches more forgiving by indexing the phonetic equivalents of words and search them by applying SOUNDEX on your search terms and then using those to search the index. With soundex mary, marie, and marry will all match, for example.