CODEWITHSUNDEEP
sqlsql-server
Caveman42
Caveman42

Reputation: 709

Adding a ' in a string SQL

I am having trouble adding a field to a string. Right now I have a stored procedure running on a giant string (everything inside '') but I am running into problems when creating the Where statement.

I have:

' Cast(Name AS varchar(max)) NOT IN (''Jimmy' + CHAR(39) + 's'')'

But I am getting an error with the CHAR(39). How can I effectively make it look like Jimmy's inside the string? I know it's probably a stupid fix, but please help.

Upvotes: 0

Views: 2211

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

You need to double it up again since the apostrophe has to survive two rounds of delimiters. Try:

' Cast(Name AS varchar(max)) NOT IN (''Jimmy''''s'')'

For example, compare:

DECLARE @sql NVARCHAR(255) = N'SELECT ''Jimmy' + CHAR(39) + ''';';
EXEC sp_executesql @sql;

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Jimmy';'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Jimmy';'.

And this:

DECLARE @sql NVARCHAR(255) = N'SELECT ''Jimmy''s'';';
EXEC sp_executesql @sql;

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ';'.

Finally:

DECLARE @sql NVARCHAR(255) = N'SELECT ''Jimmy''''s'';';
EXEC sp_executesql @sql;

Result:

-------
Jimmy's

Upvotes: 10

Related Questions