SQL STRING_ESCAPE Function Use and Examples

Overview

The STRING_ESCAPE returns special characters that are escaped so they can be part of the string. The function is valid in SQL Server 2016 and later.

Explanation

Syntax

STRING_ESCAPE(expression,rules)

Parameters

• expression – this is a string of character with special characters to escape.
• rules – the rules applied to the expression, currently the only value supported is ‘json’.

The following characters can be escaped:

Simple STRING_ESCAPE Example

The following example shows how to escape a message with special characters.

SELECT STRING_ESCAPE('/hello everybody  
 How are you \','json') as msg

The following characters are escaped:

• forward slash
• carriage return
• new line
• backslash

Example with JSON data

The following uses a variable with JSON data and special characters.

declare @jsonvar VARCHAR(max)=
'[
  {
    "firstname": "Sarah",
    "lastname": "Connor",
    "skills": ["guns\", "fight   "]
  },
  {
    "firstname": "John",
    "lastname": "Connor"
    "skills": ["hacking
         \/"]
  }
]'

If we select the variable, we will see the values escaped.

SELECT STRING_ESCAPE(@jsonvar,'json')

Show Special Characters Escaped

The following example will show some characters and the escaped values.

WITH sequence(count) AS
(
 SELECT 0
 UNION ALL
 SELECT count+1 FROM sequence WHERE count < 17
)
SELECT CONCAT('CHAR(',count,')') controlchar,STRING_ESCAPE(char([count]),'JSON') escapedcharacters FROM sequence;

Additional Information

• STRING_AGG