FULL Text Query using CONTAINS and phrase in with variable

Last Post 18 Dec 2007 04:30 PM by nathankatcgfdotorg. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
nathankatcgfdotorg
New Member
New Member

--
18 Dec 2007 03:14 PM
Here's a different one:

I have a query running against a full text index. I want to pass a query into it but it chokes. To get around that I could build the query on the fly in a string, but I hate to do that if there is a way to actually just make the query work the right way.

I'm basically doing this from BOL with a slight mod, I've just got one phrase:

======
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch ale" ')
GO
======

But I want to pass in the term in a variable:

********
DECLARE @term varchar (250)
SET @term = 'sasquatch ale'
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "' + @term + '" ')
GO
********

It get's me a syntax error though. I do something similar in sps all the time using LIKE '%%' so I'm bummed that it isn't working, of course those don't utilize double quotes. Any ideas? Again, I don't want a workaround like building the whole string if I can avoid it. I tried escaping the " and also quoted identifiers settings but...
nathankatcgfdotorg
New Member
New Member

--
18 Dec 2007 04:30 PM
Oops, yep the declare statement before GO was an error in creating the example. I'm not actually using the Northwind DB or the USE statement.

You rock! SET @term = '"sasquatch ale"' worked.

It does get me in the business of having to do this when the variable comes in from an sp:

SET @term = '"' + @term + '"'

But that's better than string building. It is bizarre that I can't just swap this:

'"' + @term + '"'

for this:

@term

...in the query. Thanks, though, great idea. I'll use it.

-N.
You are not authorized to post a reply.

Acceptable Use Policy