syntax error in CONTAINS query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DC Gringo - 12 Aug 2005 21:43 GMT

I have a CONTAINS search on a full-text index and I'm having trouble running
it on multiple terms without using an operator like AND or OR.

Here is my search query:

SELECT id
FROM table1
WHERE CONTAINS(column1,@keywords)

These work as they should:

declare @keywords varchar(25)
set @keywords = ' "word1 and word2" '

declare @keywords varchar(25)
set @keywords = ' "word1 or word2" '

...and this:

declare @keywords varchar(25)
set @keywords = ' "word1 word2" '

yields an error:

Syntax error occurred near 'failure'. Expected ''''' in search condition

Help!

_____
DC G

Reply to this Message

Hilary Cotter - 13 Aug 2005 03:30 GMT

I don't get this syntax error in SQL2000 or SQL2005. Which version are you
running?

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> I have a CONTAINS search on a full-text index and I'm having trouble running
> it on multiple terms without using an operator like AND or OR.
[quoted text clipped - 26 lines]
> _____
> DC G

Reply to this Message

John Kane - 13 Aug 2005 03:43 GMT

DC G,
You need to use the double quotes correctly... See KB article 246800
(Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at
http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800 for FTS
query examples without an @variable...

I tried the following query using the pubs table pub_info with the
FT-enabled and populated column pr_info

declare @keywords varchar(25)
set @keywords = ' "word1 word2" '
SELECT pub_id, pr_info
from pub_info where contains(pr_info,@keywords)
/* -- expected results as word1 or word2 does not exist it this column
pub_id pr_info
------ -------------

(0 row(s) affected)
*/

I tested this on a Win2003 Server with SQL Server 2000 installed. Could you
post the full output of SELECT @@version as well as FT-enable the pubs
table pub_info and run a Full population and test the above query?

Thanks,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> I have a CONTAINS search on a full-text index and I'm having trouble running
> it on multiple terms without using an operator like AND or OR.
[quoted text clipped - 26 lines]
> _____
> DC G

Reply to this Message

Daniel Crichton - 15 Aug 2005 09:43 GMT

Your use of double quotes appears to be incorrect. Everything contained
inside double quotes is classed as a phrase. Other comments are inline
below.

DC wrote on Fri, 12 Aug 2005 16:43:10 -0400:

> I have a CONTAINS search on a full-text index and I'm having trouble
> running it on multiple terms without using an operator like AND or OR.
[quoted text clipped - 8 lines]
> declare @keywords varchar(25)
> set @keywords = ' "word1 and word2" '

This is looking for the phrase "word1 and word2" in the text. If you want to
find everything that contains both words, you would use

set @keywords = ' "word1" and "word2" '

> declare @keywords varchar(25)
> set @keywords = ' "word1 or word2" '

This will search for the phrase "word1 or word2". To find all rows that
contain either word, change it to

set @keywords = ' "word1" or "word2" '

> ...and this:
>
> declare @keywords varchar(25)
> set @keywords = ' "word1 word2" '

This will search for the phrase "word1 word2". Is that what you wanted?

> yields an error:
>
> Syntax error occurred near 'failure'. Expected ''''' in search condition

Any chance of posting the exact SQL that is being executed here, because it
isn't a search for "word1 word2".

Dan

Reply to this Message

DC Gringo - 15 Aug 2005 19:48 GMT

Daniel,

My SQL is as you see it there:

declare @keywords varchar(25)
set @keywords = '"word 1 and word 2"'

SELECT id
FROM table1
WHERE CONTAINS(column1,@keywords)

I have seen this article:
http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800

...but how can I parse this according to the rules of the KB article but
using T-SQL rather than the VB?

_____
DC G

> Your use of double quotes appears to be incorrect. Everything contained
> inside double quotes is classed as a phrase. Other comments are inline
[quoted text clipped - 43 lines]
>
> Dan

Reply to this Message

John Kane - 16 Aug 2005 03:11 GMT

DC Gringo,
Checkout this article
http://www.sqlservercentral.com/columnists/ckempster/fulltextindexingtextparsing
routine.asp
it should meet your needs with a bit of customization.

Regards,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Daniel,
>
[quoted text clipped - 63 lines]
>>
>> Dan

Reply to this Message

DC Gringo - 16 Aug 2005 18:29 GMT

John,

Ok, I tried that but some work and some don't and generate the error:

Syntax error occurred near ')'. Expected '_STRING, _PREFIX_STRING' in search
condition '" coping strategies " OR ("coping" near ) OR ("coping*" near ) OR
(FORMSOF(INFLECTIONAL,"coping") )'.

Here are my summary results and failing example code using various forms of
input of multiple keywords/phrases: "coping strategies":

These work:
coping strategies
coping and strategies
coping or strategies

These do not work:
"coping strategies"
"coping" and "strategies"
"coping" or "strategies"

Here's an example of failing test code:

Declare @keywords nvarchar(500)
declare @p_searchstring nvarchar(500)
declare @ftisenabled smallint
declare @ftop nvarchar(35)

set @keywords = '"coping strategies"'
set @p_searchstring = @keywords
set @ftisenabled = 1 -- 1 is YES, we are using Full Text Indexing,
essential if catalogs are down/unavailable

if @ftisenabled = 1
begin
set @ftop = 'CONTAINS' -- so we can quickly move between different
FTI operations for testing

if ltrim(rtrim(@p_searchstring)) <> ''
begin
set @p_searchstring= replace(@p_searchstring, '%', '')
exec FTI_FixString @p_searchstring, 'near', @p_searchstring OUTPUT
end
else
set @ftisenabled = 0
end

SELECT id
FROM table1
WHERE contains(column1,@p_searchstring)

_____
DC G

> DC Gringo,
> Checkout this article
[quoted text clipped - 71 lines]
>>>
>>> Dan

Reply to this Message

Daniel Crichton - 16 Aug 2005 09:26 GMT

DC wrote on Mon, 15 Aug 2005 14:48:15 -0400:

> Daniel,
>
[quoted text clipped - 11 lines]
> ...but how can I parse this according to the rules of the KB article but
> using T-SQL rather than the VB?

You have misunderstood the parsing process. Here's the process as it is
written, taking the following string

word1 and word2

Replace all quotes: word1 and word2
(no change as there are no quotes to remove)

Surround instances of 'and' or 'and not' with quotes: word1" and "word2
(as you can see, the quotes appear either side of the and)

Surround instances of 'or' or 'or not' with quotes: word1" and "word2
(no change as these terms are not in the string)

Surround the entire string with quotes: "word1" and "word2"
(as you can see, the words are quoted, the and is not included within the
result)

If you modify the VBScript code in the article as follows (all I have done
is make it pop up the result in a msgbox dialog) and save it as a .vbs file
and run it, you'll see the correct result:

Dim strIn, RegEx, sInput

sInput = "word1 and word2"

strIn = sInput
Set RegEx = New RegExp

If Len(strIn) < 1 Then
MsgBox ("You must enter a search string")
Else
strIn = Replace(strIn, Chr(34), "")
If (InStr(strIn, "formsof") > 0) Or (InStr(strIn, "near") > 0)
Or (InStr(strIn, "isabout") > 0) Then
msgbox strIn
Else
RegEx.IgnoreCase = True
RegEx.Global = True
RegEx.Pattern = "( and not | and )"
strIn = RegEx.Replace(strIn, Chr(34) & "$1" & Chr(34))
RegEx.Pattern = "( or not | or )"
strIn = RegEx.Replace(strIn, Chr(34) & "$1" & Chr(34))
strIn = Chr(34) & strIn & Chr(34)
msgbox strIn
End If
End If

As you can see I have inserted your search terms in the strInput string.
When you run this, it will display a msgbox with

"word1" and "word2"

which is the correct quoting for requesting FTS to search for any rows with
both the words "word1" and "word2" in the column. If you use "word1 and
word2" then it will do a phrase search on that entire string, and not use
the "and" as a boolean term. Read the BOL for more details on this.

In T-SQL you could so something like this (it's a bit kludgy, so forgive me
if it's not the most efficient way of doing this)

declare @searchterms nvarchar(25)
set @searchterms = 'formsof(wibble)'

/*remove all quotes*/
set @searchterms = replace(@searchterms,char(34),'')

/*check for formsof, near, isabout*/
if charindex ('formsof',@searchterms) = 0 and charindex
('near',@searchterms) = 0 and charindex ('isabout',@searchterms) = 0
begin
/*quotes around 'and'*/
set @searchterms = replace(@searchterms,' and ','" and "')
/*fix quotes in the middle of 'and not'*/
set @searchterms = replace(@searchterms,' and "not ',' and not "')
/*quotes around 'or'*/
set @searchterms = replace(@searchterms,' or ','" or "')
/*fix quotes in middle of 'or not'*/
set @searchterms = replace(@searchterms,' or "not ',' or not "')
/*add quotes around entire string*/
set @searchterms = char(34) + @searchterms + char(34)
end

/*print the result*/
print @searchterms

Dan

Reply to this Message