How to handle-escape both single and double quotes

问题:

I have this sample T-SQL query and trying this on SQL-Server-2008.

DECLARE nvarchar(1000) @wstring = "I asked my son's teacher, "How is my son doing now?"" 

UPDATE tablename SET columnname = ' " & @wstring & " ' where ... blah ... blah

I know that the above query will throw error.

So How do I handle-escape both single and double quotes in an SQL-Update statement.

Please do not suggest about manually adding a 'slash \' or a single-quote before every quote and alike for bouble-quote.

This would be impractical because the above example is just a sAmple one and the actual application value is more than 1000 characters, which would be received from some other system-source.

回答1:

You can escape the quotes with a backslash:

"I asked my son's teacher, \"How is my son doing now?\""

回答2:

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!"'

Print:

How is my son's school helping him learn? "Not as good as Stack Overflow would!"

回答3:

Depending on what language you are programming in, you can use a function to replace double quotes with two double quotes.

For example in PHP that would be:

str_replace('"', '""', $string);

If you are trying to do that using SQL only, maybe REPLACE() is what you are looking for.

So your query would look something like this:

"UPDATE Table SET columnname = '" & REPLACE(@wstring, '"', '""') & "' where ... blah ... blah "

回答4:

Use "REPLACE" to remove special characters.

REPLACE(ColumnName ,' " ','')

Ex: -

--Query ---

DECLARE @STRING AS VARCHAR(100)
SET @STRING ='VI''RA""NJA "'

SELECT @STRING 
SELECT REPLACE(REPLACE(@STRING,'''',''),'"','') AS MY_NAME

--Result---

VI'RA""NJA"

回答5:

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks.

If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

回答6:

In C# and VB the SqlCommand object implements the Parameter.AddWithValue method which handles this situation