SQL SERVER – STRING_ESCAPE() for JSON – String Escape

2 years ago

Just another day a client of Comprehensive Database Performance Health Check, asked me if I know any way to help him immediately covert his string to support JSON formatting rules. The string which he had contained quotes as well forward slashes which are not allowed in JSON. Well, in situations like this we should consider using the function STRING_ESCAPE() which works for String Escape. Let us quickly discuss it today.

Let us first create a string with single quotes, double quotes, and forward slashes. Once we create the string let us check with the function ISJSON if the string is JSON or not.

DECLARE @String VARCHAR(100), @JSONString VARCHAR(100)
SET @String = 'single quotes(''), double quotes(""), forward slash(/)'
SET @JSONString = '[{"OurString":"'+@String+'"}]'
SELECT ISJSON(@JSONString) IsStringJSON;

When I run the script above it gives me a result as 0 telling me the string is not JSON.

Now we will take the advantage of the STRING_ESCAPE and run the same script again.

DECLARE @String VARCHAR(100), @JSONString VARCHAR(100)
SET @String = 'single quotes(''), double quotes(""), forward slash(/)'
SET @JSONString = '[{"OurString":"'+STRING_ESCAPE(@String,'JSON')+'"}]'
SELECT ISJSON(@JSONString) IsStringJSON,@JSONString

Now when you run the script above, it will return the result as 1. The JSON is now a valid JSON with the help of the string escape function.

Here are my few recent videos and I would like to know what is your feedback about them. You can subscribe to my youtube channel here.

Reference: Pinal Dave (https://blog.sqlauthority.com)