We know that certain characters in SQL need escaping – an example is the single quote – ‘
SELECT 'This is a single quote '' mark' AS EscapedCharacter;

Microsoft have even given us a special function for escaping certain characters – and at present (May 2019) – it only does JSON. So lets have a look
SELECT STRING_ESCAPE('"This is a quote ". This is a backslash \"','json') AS EscapedCharacter;

So, that’s an option that we always have.
But let’s create a table with some special JSON characters in it and see how FOR JSON AUTO treats it
IF OBJECT_ID('temp..#Characters') IS NOT NULL
BEGIN
DROP TABLE [#Characters];
END
CREATE TABLE #Characters
(
CharacterID INT IDENTITY(1,1) NOT NULL
,String NVARCHAR(100)
);
INSERT INTO #Characters (String) VALUES
('https:\\en.wikipedia.org\wiki\Albert_Einstein'),
('"This could be a quote"'),
('''This could be another quote''');
SELECT * FROM #Characters FOR JSON AUTO;
And we get the JSON
[
{
"CharacterID": 1,
"String": "https:\\\\en.wikipedia.org\\wiki\\Albert_Einstein"
},
{
"CharacterID": 2,
"String": "\"This could be a quote\""
},
{
"CharacterID": 3,
"String": "'This could be another quote'"
}
]
Which is perfectly escaped.
Notice that the JSON escape character is a backslash – \
Also notice that although we escaped the single quote character ‘ in the SQL table, the FOR JSON AUTO clause knew this was escaped in the table and addressed it appropriately in the resulting JSON.
We’ll look at more escaping of characters in the next installment.
I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.
Have a great day
Cheers
Marty
Download Files
JSON, SQL Server and Esacpe Characters of the printable kind