[sql-server-2008] How to handle-escape both single and double quotes in an SQL-Update statement

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.

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.

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

I have solved a similar problem by first importing the text into an excel spreadsheet, then using the Substitute function to replace both the single and double quotes as required by SQL Server, eg. SUBSTITUTE(SUBSTITUTE(A1, "'", "''"), """", "\""")

In my case, I had many rows (each a line of data to be cleaned then inserted) and had the spreadsheet automatically generate insert queries for the text once the substitution had been done eg. ="INSERT INTO [dbo].[tablename] ([textcolumn]) VALUES ('" & SUBSTITUTE(SUBSTITUTE(A1, "'", "''"), """", "\""") & "')"

I hope that helps.

You can escape the quotes with a backslash:

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

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"

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 "

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