SQL SERVER – STRING_ESCAPE() for JSON – String Escape

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.

• Forwarded Records and Performance – SQL in Sixty Seconds #155
• Hide Code in SSMS – SQL in Sixty Seconds #154
• Zoom in SSMS – SQL in Sixty Seconds #153
• Transfer Schema of Table – SQL in Sixty Seconds #152
• Find a Table in Execution Plan – SQL in Sixty Seconds #151

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