CODEWITHSUNDEEP
sql-serversql-server-2008
user441222
user441222

Reputation: 2001

Single-Double quotes error in Full Text Search

I am run into trouble.If type in Single-Double quotes in search statement will raise up a error,My sql statement like this:

 SELECT UsersID,Sex,Age FROM dbo.UserBasicInfo WHERE 
       CONTAINS(PositionDesired,'"*"JAVA*"')

The error message:

Msg 7630, Level 15, State 3, Line 1 Syntax error near 'JAVA*' in the full-text search condition '"*"JAVA*"'.

Assume that the result must contains Single-Double quotes like: "JAVA"PHP" How to do? Thanks !

Upvotes: 0

Views: 1483

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

You have an extra embedded double quote:

CONTAINS(PositionDesired,'"*"JAVA*"') 
----------------------------^

This effectively terminates the string early, and SQL Server doesn't understand what that extra stuff is.

Should be (I think, not a full-text guru):

CONTAINS(PositionDesired,'"*JAVA*"')

However, I think that will eliminate the error, but not return the results you are after, since punctuation is ignored. You may have to use a combination of CONTAINS and LIKE, e.g.:

CONTAINS(PositionDesired,'JAVA')
  AND PositionDesired LIKE '%"JAVA"%'

Or for the new requirement you've added:

CONTAINS(PositionDesired,'JAVA PHP')
  AND PositionDesired LIKE '%"JAVA"PHP%"'

In the LIKE clause you don't have to worry about escaping or doubling-up the double quote, because it isn't a string delimiter there.

Hopefully the CONTAINS clause will filter results first, but even in a normal query there is no guarantee of short-circuiting or order of evaluation; I have no idea about a query with full-text and standard filters.

Upvotes: 2

Related Questions