We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Full-Text Search query format

tarang
tarang asked
on
Medium Priority
245 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
Comment
Watch Question

Craig YellickDatabase Architect

Commented:
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

Author

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.
Top Expert 2006

Commented:
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.
Craig YellickDatabase Architect

Commented:
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.

Author

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.
Top Expert 2006

Commented:
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 )
Craig YellickDatabase Architect

Commented:
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

Author

Commented:
Sorry been away for a while.

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

Author

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?
Craig YellickDatabase Architect

Commented:
>> 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

Author

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?
Craig YellickDatabase Architect

Commented:
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
Database Architect
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.