SQLTeam.com | Weblogs | Forums

Full text search using CONTAINS


I have created a full text index on a text column. I now want to use CONTAINS in my queries. Let´s say that somewhere in a text field called A there exist the words "fulltextindex", "fulltexttable" and "index".

If I want to find the word "fulltextindex" I would type:

SELECT A from MyTable
WHERE CONTAINS(A, 'fulltextindex')

If I would want to find all words starting with "fulltext" I would type

SELECT A from MyTable
WHERE CONTAINS(A, '"fulltext*"')

But suppose I would want to do a full text search for all words ending with "index". How would I type that?

And how would I state a query that returned all occurencies of "text"("fulltextindex" and "fulltexttable")?


I'm ashamed to admit I've not actually used this and don't have time to check it at the moment, but I suspect something in the lines of this would work - if not let me know and I'll look when I next get time. I wouldn't usually rush a reply but looks like you've had no takers and better to hazard a guess than leave you hanging!

WHERE CONTAINS(A, '"/Index" OR "fulltext/"');

Obviously AND could be used in place or OR or alternate conditions listed one below another in your WHERE clause etc. Note take out the /s above - this site takes out stars lol.