CODEWITHSUNDEEP
sql-server-2008sql-update
MukeshAnAlsoRan
MukeshAnAlsoRan

Reputation: 671

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.

Upvotes: 42

Views: 203011

Answers (7)

Frank Ray
Frank Ray

Reputation: 73

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.

Upvotes: 0

mclark1129
mclark1129

Reputation: 7592

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

Upvotes: 27

tomf
tomf

Reputation: 51

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.

Upvotes: 5

Viranja kaushalya
Viranja kaushalya

Reputation: 785

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"

Upvotes: 0

tany4
tany4

Reputation: 194

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 "

Upvotes: 2

user2145584
user2145584

Reputation: 1

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

Upvotes: -1

Russell Zahniser
Russell Zahniser

Reputation: 16364

You can escape the quotes with a backslash:

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

Upvotes: 36

Related Questions