SQL Server – Properly Escape Characters in a String for JSON

jsonsql-server-2016t-sql

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.

Best Answer

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\\
Related Question