SQL inserting one less apostrophe in to client than on my test server

Question

I have a script inserting a formula into a field in my database. The script looks like this:

insert into Export..DataDictionary 
values(100001,  'Modifier 3',   'Modifier 3',   476,    'IsNull(Modifier_3, '''')', 'Char', NULL,   NULL,   'Y',    NULL,   NULL,   NULL,   NULL    ,'N',   NULL,   'Y',    'Modifier_3')

In my test server, it comes out correctly and the string put in the field is: IsNull(Modifier_3, '')

However, when I run the same insert on the client, the string inserts with one less apostrophe and comes out as: IsNull(Modifier_3, ')

What could be the issue here?

Answer 1

When I run into situations with quotes inside strings I use QUOTENAME.

In your case I would do like this

DECLARE @item As varchar(100) = 'IsNull(Modifier_3, {0})';
SET @item = REPLACE(@item, '{0}', QUOTENAME('', ''''));

INSERT INTO Export..DataDictionary
VALUES (..., @item, ...)

You can of course make it a one-liner if you like

REPLACE('IsNull(Modifier_3, {0})', '{0}', QUOTENAME('', ''''))