LIKE Clause question MS SQL

[raveon]

I am using a LIKE clause like so;

LIKE '%#Trim(SearchPhrase)#%'

This works for many things but it won't return a useful result for say "plumbers". If a user types this in he won't see any results for records containg "plumbing".

I assume I could truncate the search phrase to 4 letters but this could cause problems with other searches being less accurate.

Is there something other than "LIKE" I should be using that would catch a "too lengthy" search phrase?

I am coding with ColdFusion.

[lammert]

"plumbing" and "plumbers" have a lexical difference of 3. I.e. you need to replace or delete at least three characters to change one word in the other. A possibility is to add a Levenshtein function to your MSSQL query (there are some T-SQL implementations floating on the internet). Levenshtein functions are used to calculate the lexical difference between words. But this will make your queries terrible slow and will only find matches which are close in characters, not in meaning.

A practical way to achieve this kind of matching is by building an extra table which contains words you like to be treated equal. For example:

TABLE equals
ID word
1 plumber
1 plumbers
1 plumbing
2 dentist
2 dental
3 television
3 TV

and then use a query (in pseudo SQL):

SELECT yourtable.ID AS ID
FROM equals AS e1, equals AS e2, yourtable
WHERE
e1.ID = e2.ID AND
e1.word = 'plumbers' AND
yourtable.text contains e2.word

With this type of help table and query, users searching for 'TV' will also find lines in your table with the word 'television'.

The `equals` table has to be built manually and/or based on the known history of search phrases and should contain at least all the words present in your current database.