How to Escape Double Quotes Inside a SQL Fulltext 'Contains' Function

How do you escape double quotes inside a SQL fulltext 'contains' function?

From documentation:

Punctuation is ignored. Therefore, CONTAINS(testing, "computer failure") matches a row with the value, "Where is my computer? Failure to find it would be expensive."

Since FULLTEXT does not even index the punctuation, you'll need to fine-filter your results using LIKE:

SELECT  decision
FROM    table 
WHERE   CONTAINS(decision, '34 AND wide')
        AND decision LIKE '%34"%'

This will preserve the benefits of fulltext.

Escape double quotes in SQL 2005/2008

Unfortunately, double-quotes have special meaning inside FTI, so even if you parameterize it, the FTI engine treats it as a phrase delimiter. I am not sure there is an easy way to include double-quotes in an FTI search. Brackets are also a special character, but can be encased in quotes to treat as a query term - but not AFAIK double-quotes.

Update

A bit of searching suggests that doubling the quote to "" may fix it - worth a try. Personally, I'd do this inside the DB, since this is a TSQL implementation detail.

Likewise, ' needs to be doubled to '' before passing to FTI (completely separate to TSQL escaping),

Escaping Bracket [ in a CONTAINS() clause?

You don't have to escape the [ as it has no special meaning in Full Text Search. If you do need to search for an exact match though, you can use "" marks.

Further, you can use multiple "" inside the single quotes:

CONTAINS('"word1" or "word2" or "word3"')

This also works:

CONTAINS('"word1" and "word2" and "word3"')

Anything put inside the double quotes is treated as exact text. Thus if I were to do a search of the Description field of the Production.ProductDescription table in AdventureWorks, I could use

CONTAINS('shifting and "on or off-road"') 

and it would find matches for the word shifting that also had the phrase "on or off-road".

The only special symbol is the ~, it can be used in place of the NEAR command.

CONTAINS('shifting ~ smooth')

is the same as

CONTAINS('shifting NEAR smooth')

and will find matches where the words shifting and smooth are near each other.

R : doubles quotes in a paste, how to get rid of the backlashes

I would phrase your raw SQLite query as this:

select *
from pred
where
    "current.time" > '2017-10-17 00:00:00' and
    "current.time" < '2017-10-17 08:00:00';

In R, you can just use the exact above query in a character variable, e.g.

query <- paste0("select * from pred where \"current.time\" > '2017-10-17 00:00:00' ",
                "and \"current.time\" < '2017-10-17 08:00:00'")

Note that we can make a simplification to your WHERE clause and use BETWEEN, which leads to this:

query <- paste0("select * from pred where \"current.time\" between ",
                "'2017-10-17 00:00:01' and '2017-10-17 07:59:59'")

Full text query with a single quote

this gives you the two rows from your example:

SELECT  *
FROM    ft
WHERE   MATCH(value) AGAINST ('"levi\'s" lacost*' IN BOOLEAN MODE)

In http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html at the end, it talks about exact matches in double quotes. You then just escape the single quote and you are done.

Using parentheses, you can add the asterisk:

WHERE   MATCH(value) AGAINST ('(levi\'s)* lacost*' IN BOOLEAN MODE)

SQL Server Fulltext Search contains phrase problem

Why are you searching by all columns in the CIGAR table? Surely some of them do not use a string/text based data type...

After looking at the CONTAINS documentation, I'd look at a function to properly escape the words for the FTS searching:

CREATE FUNCTION [dbo].[escapeFTSSearch] (
  @SearchParameter NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
  -- Declare the return variable here
  DECLARE @result NVARCHAR(MAX)

  SELECT @result = '"'+ REPLACE(REPLACE(@SearchParameter,'"',''), ' ', '" AND "') +'"'

  -- Return the result of the function
  RETURN @result

END

Test:

SELECT [example].[dbo].[escapeFTSSearch] ('Punch Cigar')

...which gives me:

"Punch" AND "Cigar"

Usage:

WHERE CONTAINS(cigars.*, dbo.escapeFTSSearch(@Search) )

Addendum

The function is simplistic:

• it assumes you want all words provided
• doesn't support fuzzy searching
• assumes double quotes aren't in the parameter value

Tweak as needed.

JCR-SQL - contains function doesn't escape special characters?

I'm not sure, if jcr:contains is the right method for you. Maybe jcr:like is the better approach for what you want.

jcr:contains is a fulltext search, and uses a lucene index. So it might have some unexpected impacts. It also cannot so easily combined with other indexes.

jcr:like is a attribute comparison with wildcards. And this wildcards can be escaped with a backslash. (https://docs.adobe.com/docs/en/spec/jcr/1.0/6.6.5.1_jcr_like_Function.html)

---

1st Example SQL-2 Query

Searches cq:PageContent nodes with an * in any attribute. The % (percent-sign) is the wildcard-symbol. The * is searched.

SELECT * FROM [cq:PageContent] AS content
WHERE ISDESCENDANTNODE('/content/myproject/...')
AND content.* LIKE '%*%'

---

2nd Example SQL-2 Query

Searches cq:PageContent nodes with an % in any attribute. Therefore the percent-sign is escaped with \% (and surrounded by the wildcard %).

SELECT * FROM [cq:PageContent] AS content
WHERE ISDESCENDANTNODE('/content/myproject/...')
AND content.* LIKE '%\%%'

---

3rd Example XPath Query

Almost the same as the last one, just as XPath query. Only I just don't know, how you can search for any attribute. So this example searches for jcr:title attributes.

/jcr:root/content/myproject/...//element(*, cq:PageContent)[jcr:like(@jcr:title, '%\%%')]

---

Related Topics