{"id":4071,"date":"2019-05-19T20:19:39","date_gmt":"2019-05-20T03:19:39","guid":{"rendered":"https:\/\/database.guide\/?p=4071"},"modified":"2019-11-19T22:57:05","modified_gmt":"2019-11-20T05:57:05","slug":"how-the-string_escape-function-works-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/how-the-string_escape-function-works-in-sql-server-t-sql\/","title":{"rendered":"How the STRING_ESCAPE() Function Works in SQL Server (T-SQL)"},"content":{"rendered":"

In SQL Server<\/a>, the T-SQL<\/a>\u00a0STRING_ESCAPE()<\/code> function escapes special characters in texts and returns the text with escaped characters.<\/p>\n

You provide the text and the escape type as arguments when calling the function.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

The syntax goes like this:<\/p>\n

STRING_ESCAPE( text , type )<\/pre>\n
Where text<\/var> is the text that contains any characters to be escaped, and type<\/var> determines the escaping rules to apply. <\/p>\n
Currently, the only value supported for the type<\/var> argument is 'json'<\/code>.<\/p>\n
Example 1 – Basic Usage<\/h2>\n
Here’s an example of how it works:<\/p>\n
SELECT STRING_ESCAPE('\\', 'json') AS Result;<\/pre>\n
Result:<\/p>\n
\r\n+----------+\r\n| Result   |\r\n|----------|\r\n| \\\\       |\r\n+----------+\r\n<\/pre>\n
Here’s an example where a forward slash is escaped:<\/p>\n
SELECT STRING_ESCAPE('\/', 'json') AS Result;<\/pre>\n
Result:<\/p>\n
\r\n+----------+\r\n| Result   |\r\n|----------|\r\n| \\\/       |\r\n+----------+\r\n<\/pre>\n
And here’s an example with a bit more text:<\/p>\n
SELECT STRING_ESCAPE('Free beer\/wine with every lambo!', 'json') AS Result;<\/pre>\n
Result:<\/p>\n
\r\n+-----------------------------------+\r\n| Result                            |\r\n|-----------------------------------|\r\n| Free beer\\\/wine with every lambo! |\r\n+-----------------------------------+\r\n<\/pre>\n
Example 2 – Invalid Type<\/h2>\n
At the time of writing, the only value supported for the type<\/var> parameter is 'json'<\/code>. Here’s what happens if we supply an unsupported value:<\/p>\n
SELECT STRING_ESCAPE('\\', 'oops') AS Result;<\/pre>\n
Result:<\/p>\n
\r\nAn invalid value was specified for argument 2.\r\n<\/pre>\n
Escapable Characters<\/h2>\n
At the time of writing, the STRING_ESCAPE()<\/code> function can only escape the JSON special characters listed in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
Special character<\/th>\nEncoded sequence<\/th>\n<\/tr>\n<\/thead>\n
Quotation mark (")<\/td>\n\\"<\/td>\n<\/tr>\n
Reverse solidus (\\)<\/td>\n\\|<\/td>\n<\/tr>\n
Solidus (\/)<\/td>\n\\\/<\/td>\n<\/tr>\n
Backspace<\/td>\n\\b<\/td>\n<\/tr>\n
Form feed<\/td>\n\\f<\/td>\n<\/tr>\n
New line<\/td>\n\\n<\/td>\n<\/tr>\n
Carriage return<\/td>\n\\r<\/td>\n<\/tr>\n
Horizontal tab<\/td>\n\\t<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n\n
Control character<\/th>\nEncoded sequence<\/th>\n<\/tr>\n<\/thead>\n
CHAR(0)<\/td>\n\\u0000<\/td>\n<\/tr>\n
CHAR(1)<\/td>\n\\u0001<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n<\/tr>\n
CHAR(31)<\/td>\n\\u001f<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
In SQL Server, the T-SQL\u00a0STRING_ESCAPE() function escapes special characters in texts and returns the text with escaped characters. You provide the text and the escape type as arguments when calling the function.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[93,77,67,61],"class_list":["post-4071","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-functions","tag-mssql","tag-string-functions","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/4071","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=4071"}],"version-history":[{"count":2,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/4071\/revisions"}],"predecessor-version":[{"id":4073,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/4071\/revisions\/4073"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=4071"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=4071"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=4071"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}