Re: CONTAINS from a list

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: DaveK (DaveK_at_discussions.microsoft.com)
Date: 11/05/04 

Date: Fri, 5 Nov 2004 09:35:07 -0800

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))
INSERT INTO #MySearchWords SELECT '%Onions%' UNION ALL SELECT '%computer%'

SELECT T.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=eYjp%23SLFBHA.1352%40tkmsftngp02&output=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
>
>
>

Relevant Pages

  • Re: fetching values returned by Stored Procedure by columnName and not
    ... yes, I am looping through the rows, but we are using SqlDataReader ... Following is the query in it. ... int fullName = offset + 1; ... int ShortName = offset + 2; ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Problem creating access Query on the fly
    ... I also found that by looping through all the ... 'Tables' in the database, some of the queries are found. ... I would Like to create a query in an Access database ...
    (microsoft.public.data.ado)
  • Re: Garbage Collection
    ... i mistakenly write there is no option looping, ... option but wat my query is that is this the only option, cant i dispose ...
    (microsoft.public.dotnet.framework.clr)
  • Re: Getting Record Count from SELECTs
    ... say you have a query as follows: ... type array is table of number index by binary_integer; ... 13 end loop; ... Looping, c%rowcount = 5 ...
    (comp.databases.oracle.server)
  • required suggestion
    ... Visual Studio.NET, SQL Server 2000; ... I've table where I've to insert some data from a query and then while ... looping for each row, I want to make it as a parameter and get some other ... JB Securities Pvt. ...
    (microsoft.public.data.ado)