Menu
I have an international company that has recently been added, which is named "BLA "BLAHBLAH" Ltd. (The double quotes are part of the name. )
Whenever a user tries to search for this company, by entering "Blah, or something to that affect, the search fails with a syntax error in SQL server.
How can I escape this so the search will not fail?
Sample SQL:
SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect
FROM RussoundGeneral.dbo.Company c
LEFT JOIN RussoundGeneral.dbo.CompanyMax cm
ON (cm.companyId = c.companyId and cm.maxID is not null)
WHERE CONTAINS ( companyName, '"BLAH*' )
GROUP BY c.companyID, c.companyName, c.dateAdded
ORDER BY c.companyName ASC
470
Use two single quotes to escape them in the sql statement. The double quotes should not be a problem: SELECT 'How is my son''s school helping him learn? "Not as good as Stack Overflow would!"'
Double quotes generally aren't used in SQL, but that can vary from database to database. Stick to using single quotes.
If you need to use single quotes and double quotes in a string that contains both a contraction and a quote, you will need to use the backslash '' to cancel out the following character.
In ANSI SQL, the backslash character (\) is the escape character. To search for data that begins with the string \abc , the WHERE clause must use an escape character as follows: ... where col1 = '\\abc';
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),
120
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
