finding a word, submitting part of it

Dear All

Does anyone know any syntax that will allow me to find entries in a atable when only part of a word is entered. I have a table that has been full txt indexed at the moment I am using the CONTAINS keyword to search fields. This works fine 'select * from tblX where contains(colX, 'gill')', gives me back what i want, but what if i type this 'select * from tblX where contains(colX, 'ill')' it doesn't find anything. What i want is a command that will search all the words in a table column. Does anyone know of a command that will do this?

ThanksI got into the CONTAINS (T-SQL) syntax, and I found only prefix_terms like 'ill*', but I didn't found a solution for a '*ill' condition. Using the LIKE operator isn't an option either. I hope that other guys can help you. :(|||Well, LIKE is an option, just not a very efficient one. But, it's there for just this type of search.

SELECT * FROM tblX WHERE colX LIKE '%ill'

You can also replace the wildcard % with:

_ looks for any single char

[] looks for any chars or range of chars you specify ([a-q], [abcd], [1-9], etc)

[^] same as above, but exclusion chars ([^a-f] means anything but a through f)|||Another option is to use CHARINDEX:

where charindex('ill', colx) > 0

I don't know how this compares to LIKE for speed, but I suspect it would be faster because it has less functionality (no wildcards) and thus may have less processing overhead.

blindman|||I think you are right. Though, any time you ask SQL to do string processing, you are going to take a hit. They seem to have build in several levels of functionality here, getting progressively deeper as you need them. Can't think of why they would do that vs a sigle flexible function, unless the performance would dive.