CODEWITHSUNDEEP
sqlsql-serverescapingsingle-quotes
user7432713
user7432713

Reputation: 226

Put single quotes into single quotes in SQL Server string

I have the update query below.

update tableName 
set columnName = null 
where isnull(columnName, '') = ''

I want to put single quotes in a SQL Server string builder query so that the above query can be executed for every column in the table. See this query:

Declare @sql2 varchar(max) = ''
declare @tablename2 as varchar(255) = 'test2'
select @sql2 = @sql2 + 'update [' + @tablename2 + '] set [' + c.name +']' + ' = NULL ' +
        ' WHERE ISNULL([' + c.name + '], ' + '' + ') = ' + ''
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id 
where t.name = @tablename2
EXEC (@sql2)
go

Below is test data.

create table test2 
(
    test varchar(50)
)

insert into test2 
values (' ewewwe'), ('sdsddsds '), ('')

I get this error while executing the SQL String builder query:

Incorrect syntax near ') = '

What am I doing wrong?

Upvotes: 0

Views: 3273

Answers (3)

John Sørensen
John Sørensen

Reputation: 126

The error you receive is because the statement string is not valid. You end up with an unescaped string.

You need to add an escaped quote ('') for each quote you need, ('''''') like this:

Declare @sql2 varchar(max) =''
declare @tablename2 as varchar(255) ='test2'
select @sql2 = @sql2 + 'update [' + @tablename2 + '] set [' + c.name + ']' + ' = NULL ' +
        ' WHERE ISNULL([' + c.name + '], ' + '''''' + ') = ' + ''''''
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id 
where t.name = @tablename2
EXEC (@sql2)
go

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

Single quotes are self-escaped; that is, you put two together in a literal to get one in the final string.

Additionally, you want the QUOTENAME() function to handle enclosing these fields. It's smart enough to also account for names that might include braces or other weirdness, plus you can expect it to be updated if anything else is ever added to the language that might interfere:

DECLARE @sql2 varchar(max) = ''
DECLARE @tablename2 as varchar(255) = 'test2'

SELECT @sql2 = @sql2 + 
     'update ' + QUOTENAME(@tablename2) +
     ' set ' + QUOTENAME(c.name) + ' = NULL' +
     ' WHERE COALESCE(RTRIM(' + QUOTENAME(c.name) + '), '''') = '''' '
FROM sys.columns c
INNER JOIN sys.tables t on c.object_id = t.object_id 
WHERE t.name = @tablename2

EXEC(@sql2)

Upvotes: 0

zip
zip

Reputation: 4061

when you use isnull, you have to provide 2 parameters

' WHERE ISNULL([' + c.name + '], ' + '???' + ') = ' + ''

You need to provide something for the ??? and currently its empty

Replace with

WHERE ISNULL([' + c.name + '], ' + '''''' + ')

Upvotes: 1

Related Questions