Syntax error on Free Text search

[Craftor]

Hi all

I have a database that is full text indexed. I am trying to search for a string that contains a single quote ('). When I try to do a SELECT statement with CONTAINS, it gives me the following error:

Syntax error occurred near 'wants'. Expected ''''' in search condition 'Mary-Lou wants Anna's purse strings untied.'.

I've tried escaping the single quote with two quotes but it doesn't seem to make a differene.

Can anyone help me with this, please?

Thanks as always



Craftor

 

[JamesLean]

It might help if we saw your SELECT statement...

--James

 

[Craftor]

UPDATE:

It's, in fact, not a normal single quote but a funny angled quote (’). Don't know if this will display on everyone's browser so please let me know if it is not displaying correctly.

JamesLean - I've updated the SQL statement to enclose my string with double quotes was still getting nothing back until I found that the quote was being stored as an XML Escaped character in the DB (&#x2019.

Is it just wishful thinking to hope that there is an elegant way of doing this on the query side without using the REPLACE() function or is that, in fact, the best way?

My SELECT statement currently looks like:

declare @keywords VARCHAR(1000)
set @keywords = '"Mary-Lou wants  Anna’s purse strings untied"'
set @keywords = replace(@keywords, '’', '’')

Select 	*
From 	ArticleDetail 
Where 	Contains(*, @keywords )

@keywords is the parameter being passed to my stored proc - populated here so you can see what I am doing.

Thanks as always



Craftor

 

[JamesLean]

I would say using REPLACE is fine. Can't think how else you'd do it.

--James

 

[Craftor]

Thanks JamesLean - appreciate it!