?
Solved

Full-Text Search query format

Posted on 2006-04-03
13
Medium Priority
?
228 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:tarang
  • 6
  • 5
  • 2
13 Comments
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 16366900
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
 
LVL 2

Author Comment

by:tarang
ID: 16367444
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16368160
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Expert Comment

by:Craig Yellick
ID: 16370605
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
 
LVL 2

Author Comment

by:tarang
ID: 16377993
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16378486
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
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 16379404
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
 
LVL 2

Author Comment

by:tarang
ID: 16414706
Sorry been away for a while.

Yes it works.  But the original problem of how to get all the words remains.
0
 
LVL 2

Author Comment

by:tarang
ID: 16414832
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
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 16417530
>> 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
 
LVL 2

Author Comment

by:tarang
ID: 16418356
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
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 16418673
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
 
LVL 11

Accepted Solution

by:
Craig Yellick earned 2000 total points
ID: 16418809
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question