How to handle-escape both single and double quotes in an SQL-Update statement

Question

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        amp   wstring  amp      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

User · Answer

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 "

User · Answer

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

User · Answer

You can escape the quotes with a backslash    I asked my son s teacher    How is my son doing now

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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      amp   SUBSTITUTE SUBSTITUTE A1                            amp        I hope that helps

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

Examples related to sql-server-2008

Examples related to sql-update