Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


NEW T-SQL FEATURES IN SQL SERVER 2016- VI [ STRING_ESCAPE() ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

New feature – VI | [ STRING_ESCAPE() ]

Escapes special characters in texts and returns text with escaped characters. STRING_ESCAPE is a deterministic function. In my next post I shall explain what is deterministic function.

Notes:-

1. It returns nvarchar(max) text with escaped special and control characters.
2. It currently only escape JSON special characters ( Refer below table ).

Special character Encoded sequence
Quotation mark (“) \”
Reverse solidus (\) \\
Solidus (/) \/
Backspace \b
Form feed \f
New line \n
Carriage return \r
Horizontal tab \t

STRING_ESCAPE example

--

SELECT STRING_ESCAPE('\Pawan', 'json') AS NewTextWithAddedEscapeCharacters; 

--

OUTPUT

--

NewTextWithAddedEscapeCharacters
---------------------------------------
\\Pawan

(1 row affected)

--

Example with JSON Formatted Object

--

DECLARE @json AS VARCHAR(MAX)=''
SET @json = FORMATMESSAGE('{ "id": %d,"name": "%s", "surname": "%s" }', 1, STRING_ESCAPE('Pawan','json'), STRING_ESCAPE('Khowal','json') );  

SELECT @json

--

OUTPUT

--

---------------------------------------------------
{ "id": 1,"name": "Pawan", "surname": "Khowal" }

(1 row affected)

--

Refer below for more details-

1.https://docs.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements