Full-Text Search query format

I am trying to come up with the syntax to do a full text serach on a field within a table based upon words stored in a field in another table.  So I have table on which I want to perform the search.  The table contains a text fields called [OutputText] and an [ID] field.  Another table contains [SearchWords] and [GroupCode].  I know what the [GroupCode] is and based upon that [GroupCode] I want to find all matching [ID]s.  

SELECT [ID] FROM [TOutput] AS TO,
    ( SELECT [SearchWords] FROM [TWords]
    WHERE [GroupCode]=1 ) AS TW
WHERE
    CONTAINS ( [TO].[OutputText],[TW].[SearchWords])

The problem is that the CONTAINS statement will not accept [TW].[SearchWords] as a parameter.

What syntax should I use to accomplish my objective.

Thanks
LVL 2
tarangAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Craig YellickDatabase ArchitectCommented:
Does the subquery

    SELECT [SearchWords] FROM [TWords] WHERE [GroupCode]=1

return multiple records? I'm guessing yes. Do you want to find all of the words (AND) or any of the words (OR) with exact matches? There are a *lot* of options.

Let's start simple and work up to your full problem. Tweak the following to return something, so we have a starting point that works.

  declare @words varchar(100)
  set @words = 'testing'

  SELECT [ID] FROM [TOutput] AS TO
  WHERE
    CONTAINS ( [TO].[OutputText], @words )

Then do a single-value lookup out of your SearchWords table instead of the constant value.

   set @words = (select top 1 [SearchWords] FROM [TWords] WHERE [GroupCode]=1)

Assuming that works, the real challenge will be to get a list of 1..N search words concatenated together with the AND / OR / NEAR operators.  A user-defined function can accomplish that pretty nicely.

-- Craig
0
tarangAuthor Commented:
Yes you are correct that SELECT [SearchWords] FROM [TWords] WHERE [GroupCode]=1 will return multiple records.

The example you gave where @words='testing' works however the select top 1 statement did not work.
0
imran_fastCommented:
use this
select top 1 @words =  [SearchWords] FROM [TWords] WHERE [GroupCode]=1
instead of
set @words = (select top 1 [SearchWords] FROM [TWords] WHERE [GroupCode]=1)
in Craig post.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Craig YellickDatabase ArchitectCommented:
TOP 1 needs an ORDER BY ... I was typing that off my head and did not test it. The point was to perform a SELECT and get a single search word from your table, so use whatever technique that works for you. This is an intermediate step. You must be able to use a single word from your search word list before moving on to handling multiple words.
0
tarangAuthor Commented:
I guess that works at least syntacticly but it seems one of my basic assumptions does not work.  That is the CONTAINS statement does not seem to return a result.  For example if the text field contained the word 'watch' and I did the query using 'watch' I get back no results.  So I guess I have a more fundamental problem.  Hence I'm doubling the points.
0
imran_fastCommented:
did you try using ' "*watch*" ' in search instead of 'watch'
======================================
declare @words varchar(100)

select top 1 @words =  [SearchWords] FROM [TWords] WHERE [GroupCode]=1
set @words = '"*'+ltrim(rtrim(@words))+'*"'

  SELECT [ID] FROM [TOutput] AS [TO]
  WHERE
    CONTAINS ( [TO].[OutputText], @words )
0
Craig YellickDatabase ArchitectCommented:
The query:

SELECT [ID] FROM [TOutput] AS TO
  WHERE
    CONTAINS ( [TO].[OutputText], 'watch' )

should work if the OutputText column does indeed contain 'watch'.

If it does not return rows (as opposed to displaying an error message) then the full-text catalog has been defined but the indexing job has never run or is scheduled to run but has not yet run, is scheduled to run but failed (check the job history) or ran successfully but is not set to update the index and hits for 'watch' were added or modified since the job was last run.

-- Craig

0
tarangAuthor Commented:
Sorry been away for a while.

Yes it works.  But the original problem of how to get all the words remains.
0
tarangAuthor Commented:
DECLARE @vsWord      varchar(128)

DECLARE cWords CURSOR FOR
SELECT sWord FROM TWords WHERE iID=1

SELECT sText INTO #tText FROM TData WHERE iID=0

OPEN cWords
FETCH NEXT FROM cWords INTO @vsWord
WHILE (@@FETCH_STATUS=0)
BEGIN
      INSERT INTO #tText SELECT sText FROM TData WHERE CONTAINS(tText,@vsWord)
      FETCH NEXT FROM cWords INTO @vsWord
END

CLOSE cWords
DEALLOCATE cWords

SELECT * FROM #tText GROUP BY sText
DROP TABLE #tText


The above accomplishes what I want but I wonder if there is a more efficient way of doing it?
0
Craig YellickDatabase ArchitectCommented:
>> yes it works

Good. I understand you want to do more. I'm trying to establish a baseline. No point in troubleshooting a complicated query if even a simple query won't work due to config problems.

>> Is there a more efficient way

Yes.

Next step is to verify that a query involving a list of words with OR works as desired. Below, a simulation of what happens when you use a GroupCode that returns multiple words.

   SELECT [ID] FROM [TOutput] AS TO
     WHERE  CONTAINS ( [TO].[OutputText], " 'watch' or 'other' or 'more' " )

If that works, convert the constant to a variable.

  declare @words varchar(1000)
  set @words = " 'watch' or 'other' or 'more' "
  SELECT [ID] FROM [TOutput] AS TO
     WHERE  CONTAINS ( [TO].[OutputText], @words )

Assuming it's still working, all you need to do is construct the list of words with OR between each. That can be done with a scalar user-defined function.  Below, the function is named dbo.WordListForGroup and is building it for GroupCode=1.

    SELECT [ID] FROM [TOutput] AS TO
     WHERE  CONTAINS ( [TO].[OutputText], dbo.WordListForGroup(1) )

Let me know if this is on the right track.

-- Craig
0
tarangAuthor Commented:
The problem I see is

declare @words varchar(1000)

In theory it is possible that I may need more than a 1000 char.  How can that be taken into account?
0
Craig YellickDatabase ArchitectCommented:
I picked 1000 out of the air.  In SQL 2000 the largest is:

  declare @words varchar(8000)

And in SQL 2005 there is no practical limit, something like 2 Gb.

  declare @words varchar(max)

Are you using SQL Server 2005? If so, building the list of words is much, much easier. In SQL 2000 building the word string requires a cursor.

-- Craig
0
Craig YellickDatabase ArchitectCommented:
Assuming you want a SQL 2000 solution the user-defined function would look something like:

create function WordListForGroup(@GroupCode integer)
returns varchar(8000) as begin
  declare @s varchar(8000)
  declare @word varchar(100)
  declare cur cursor local for
     select SearchWords from TWords
     where GroupCode=@GroupCode
  open cur
  fetch next from cur into @word
  if @@fetch_status = 0 set @s = @word
  while @@fetch_status = 0 begin
    fetch next from cur into @word
    if @@fetch_status = 0 set @s = @s + ' or ' + @word
  end
  close cur
  deallocate cur
  return @s
end


Also, I was surprised to find that you cannot call a UDF in the CONTAINS function. Or, if you can, I could not get the syntax right.

    SELECT [ID] FROM [TOutput] AS TO  /* Does NOT work */
     WHERE  CONTAINS ( [TO].[OutputText], dbo.WordListForGroup(1) )

Instead the UDF will have to assign a variable, like so:

  declare @words varchar(8000)
  set @words = dbo.WordListForGroup(1)

    SELECT [ID] FROM [TOutput] AS TO  /* Works */
     WHERE  CONTAINS ( [TO].[OutputText], @words)

-- Craig
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.