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

  • Has something gone wrong?
  • Is this valid JSON?

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

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

Leave a Reply

Your email address will not be published.