JSON, SQL Server and Esacpe Characters of the non-printable kind

G’day,

We observed in a previous installment that JSON uses the backslash character “” as the escape character.

However, what happens if we actually want a backslash in our sting.

Well, We just escape that with another backslash

SELECT STRING_ESCAPE('' , 'json');

Which gives (simply)

So when we see a JSON document like this

[
  {
    "Character": "n"
  },
  {
    "Character": "r"
  }
]

Then we might wonder

The answer would be that everything is fine and this is perfectly valid JSON.

Why?

Because r and n are both non-printable characters.

You’ll see this is valid if you use STRING_ESCAPE

SELECT STRING_ESCAPE(CHAR(10) , 'json') AS [Character]
UNION
SELECT STRING_ESCAPE(CHAR(13) , 'json');

Which gives

Char(10) – ASCII 10 – is the new line character, while CHAT(13) – ASCII 13 – is the carriage return character.

If you’d like to look at other examples of non printable characters then you can play with the STRING_EXCAPE and ASCII T-SQL functions

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

Rate