MS SQL Server STRING_ESCAPE() Function

Sometimes you may need to create JSON text from variables and escapes any special JSON character in variables in MS SQL Server. There is a function called STRING_ESCAPE in MS SQL Server by which you can do this. STRING_ESCAPE function escapes special characters in texts and returns text with escaped characters. This function is introduced in SQL Server 2016 and later.

Let’s see the function syntax:

STRING_ESCAPE(text, type);

In this syntax:

• Parameter text is a nvarchar expression representing the object that should be escaped.
• Parameter type escaping rules that will be applied. Currently the value supported is 'json'.
• Function return nvarchar(max) text with escaped special and control characters. Currently STRING_ESCAPE can only escape JSON special characters.

Now, let's see an example. Look into the following example which creates JSON text from number and string variables, and escapes any special JSON character in variables.
SET @json = FORMATMESSAGE('{ "id": %d,"name": "%s", "address": "%s" }',
    1, STRING_ESCAPE(@name,'json'), STRING_ESCAPE(@address,'json') );

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to create JSON text from variables and escapes any special JSON character in variables using STRING_ESCAPE function in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.