Single quotes inside FREETEXT Search arguments.

[Guest_imported]

I have created a full-text catalogue for a CustomerList table, and I tried to execute the following query to find all records whose CompanyName field contains the string "Gold's Gym." In a normal select statement, you'd do something like this:

SELECT * FROM CustomerList WHERE CompanyName LIKE '%Gold''s Gym%'

But, since we are using the full-text catalogue, we have to use the contains clause. So I tried this:

SELECT * FROM Customerist WHERE FREETEXT(CompanyName,'Gold''s Gym')

That gives me an error:

Syntax error in search parameter 'Gold's Gym'

So obviously, SQL is interpretting the double sinel-quotes as a literal single quote, but it's not liking it. Why is that? Thanks in advance.

 

[tlbroadbent]

Try this.

SELECT * FROM Customerist WHERE FREETEXT(CompanyName,"Gold's Gym&quot
Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[Guest_imported]

It didn't like that either.. same error..I'm starting to wonder if the FREETEXT clause allows quotes inside it. but I can't imagine why it wouldn't. That would be pretty pathetic of Microsoft.

Xin Li

 

[tlbroadbent]

Did you use double quotes around "Gold's Gym" rather than two single quotes? Just checking. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[tlbroadbent]

You might try using a variable as the search parameter.

DECLARE @str varchar(30)
SET @str ="Gold''s Gym"
SELECT * FROM CustomerList
WHERE FREETEXT(CompanyName, @str)

Another possiblity is executing the query via Execute or sp_executesql.

Declare @sql varchar(256)
set @sql="SELECT * FROM Customerist WHERE FREETEXT(CompanyName,'Gold''s Gym')"
Execute(@sql) /* Or sp_executesql @sql */ Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot