Using single quote in an Exec statement in SQL

Question

I am having issues executing a SQL statement from a stored proc having single quotes. Here is my query from a stored procedure that I am executing.

EXEC('UPDATE myTABLE 
         SET myCOLUMN = (SELECT Replace('OSINGLEQUOTEJOHN DOE','SINGLEQUOTE','''')')

I am trying to update table "myTABLE" column "myCOLUMN" with a value "O'John Doe"

The actual query is like this, i tried to simplify it a bit in the above example

EXEC('UPDATE myTABLE 
         SET myCOLUMN = (SELECT Replace('+ @IntegrationGuardian2FullName +','SINGLEQUOTE','''')')

The value of @IntegrationGuardian2FullName is "OSINGLEQUOTEJOHN DOE". Hope that makes more sense.

Can any body help me formatting this query?

Answer 1

 UPDATE myTABLE SET myCOLUMN='O''John Doe'

 EXEC('UPDATE myTABLE SET myCOLUMN=''O''John Doe''')

Answer 2

You need to double-escape the single quote inside the quoted string - so use two single quotes to wrap the string, and four (i.e. two escaped quotes) which will be un-escaped into a pair of single quotes when the query is parsed, and then un-escaped again into the single quote you actually want to insert:

EXEC('UPDATE myTable SET myColumn = ''John''''O Doe''')

Answer 3

Use:

EXEC('UPDATE myTABLE 
        SET myCOLUMN = (SELECT REPLACE(''OSINGLEQUOTEJOHN DOE'',
                                       ''SINGLEQUOTE'',
                                       ''''''''))')

What you provided needed two single quotes around the string, and what to replace, and additional single quotes because of the single quote escaping necessary for dynamic SQL.