CONTAINS from a list

Hi, What is the quickest way to do a CONTAINS on words from a list stored
seperately? E.g if i had table such as
CREATE TABLE TestTerms (PkColumn Int, TextValue VARCHAR(30))
INSERT INTO TestTerms SELECT 1, 'envelope' UNION ALL
SELECT 1, 'book' UNION ALL
SELECT 3, 'pen'
I want to do something like:
SELECT * FROM MyMainTable WHERE CONTAINS(Product, (SELECT TextValue FROM
TestTerms))
How could I do that without looping through the words? Many thanks
I think you are probably trying to dynamically build a list that does a
Boolean OR.
Here is an example of such a query:
declare @.str varchar(2000)
set @.str=char(34)
select @.str=@.str+au_lname+char(34)+' OR ' from authors
select @.str=left(@.str,len(@.str)-3)
print @.str
It is also possible that you are trying to incorporate a thesaurus like
function.
Here is a post which explains how to do this.
http://groups.google.com/groups?selm...utpu t=gplain
Perhaps if you were to explain what you are to accomplish I could help you
better.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:DDD82F8D-9178-486F-96BA-A15F0E86AE39@.microsoft.com...
> Hi, What is the quickest way to do a CONTAINS on words from a list stored
> seperately? E.g if i had table such as
> CREATE TABLE TestTerms (PkColumn Int, TextValue VARCHAR(30))
> INSERT INTO TestTerms SELECT 1, 'envelope' UNION ALL
> SELECT 1, 'book' UNION ALL
> SELECT 3, 'pen'
> I want to do something like:
> SELECT * FROM MyMainTable WHERE CONTAINS(Product, (SELECT TextValue FROM
> TestTerms))
> How could I do that without looping through the words? Many thanks
|||Thanks Hilary. Basically, it's simply OR'ing. Using my example I want to
return all products containing "book" or "pen" etc.. in their description.
Rather than looping through them all I wanted to try and see whether I can do
one search. For example, a non-FTI approach could be:
USE PUBS
CREATE TABLE #MySearchWords (TextValue VARCHAR(30))
INSERTINTO #MySearchWords SELECT '%Onions%' UNION ALL SELECT '%computer%'
SELECTT.Title FROM Titles T
JOIN #MySearchWords MSW ON T.Title LIKE MSW.TextValue
This allows the input search values to be joined against the table without
having to loop through each one. I want to emulate something like that in FTI
if poss?
Thanks
"Hilary Cotter" wrote:

> I think you are probably trying to dynamically build a list that does a
> Boolean OR.
> Here is an example of such a query:
> declare @.str varchar(2000)
> set @.str=char(34)
> select @.str=@.str+au_lname+char(34)+' OR ' from authors
> select @.str=left(@.str,len(@.str)-3)
> print @.str
> It is also possible that you are trying to incorporate a thesaurus like
> function.
> Here is a post which explains how to do this.
> http://groups.google.com/groups?selm...utpu t=gplain
> Perhaps if you were to explain what you are to accomplish I could help you
> better.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "DaveK" <DaveK@.discussions.microsoft.com> wrote in message
> news:DDD82F8D-9178-486F-96BA-A15F0E86AE39@.microsoft.com...
>
>