2

With the below example, how do I escape the double-quote characters that are in the @data variable?

declare @data nvarchar(max) = N'"TEST"';
declare @jsonFragment nvarchar(max);
declare @id int = 999;

set @jsonFragment = ',"' + cast(@id as nvarchar(16)) + '":"' + @data + '"';

select @jsonFragment;

The result is:

 ,"999":""TEST""

I need it to be:

 ,"999":"\"TEST\""

As I understand it, selecting for json path requires that I define a static key name - but as can be seen, I have a scenario where key names are being defined dynamically.

In my real-world use case, this string construction is occurring within a stuff statement, itself part of a column definition in a larger select that utilises CTEs - meaning it would be very hard to create a dynamic sql statement to be run using sp_executeSQL.

2

There is a system function for this purpose called STRING_ESCAPE that will escape the characters as needed for a given string type.

In your example:

declare @data nvarchar(max) = N'"TEST"';
declare @jsonFragment nvarchar(max);
declare @id int = 999;

set @jsonFragment = ',"' + cast(@id as nvarchar(16)) + '":"' + STRING_ESCAPE(@data, 'json') + '"';

select @jsonFragment;

Returns:

,"999":"\"TEST\""

It works with all elements that need to be escaped, such as slashes in a pathname, for example:

SELECT STRING_ESCAPE(N'C:\PathName\Temp\', 'json')

Returns

C:\\PathName\\Temp\\
  • Aha. This sounds like what I'm looking for. My web search skills must suck because I couldn't find it! Will test this when back to my PC. Thanks. – youcantryreachingme May 28 '19 at 7:46
  • Thank you. So simple. Hope the question helps others find this system function. – youcantryreachingme May 28 '19 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.