JSON, SQL Server and Escape Characters of the printable kind

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