Posted in Development

SQL Server – String_Escape function in SQL Server 2016

Hi friends, in SQL server 2016 another string function that introduced is String_Escape. This function can escape special characters within texts and will return text with escaped characters.

Syntax

STRING_ESCAPE (expression, type)

Currently only the value that is supported for type argument is ‘json’ only. If you try to specify another type it will result into error stating ‘An invalid value was specified for argument 2’.

In expression any nvarchar expression can be specified that needs to be escaped.

Currently, only limited JASON escape characters can be escaped.

Here is the below script that is used to show the escaped characters like this –

SELECT string_escape(‘/

” \/

 

‘,’json’)

It will results in this manner –

string_escape

As we can see in result set the escaped characters like new line, carriage return, double quotes etc. for the expression that we specified.

Let’s check another example in which I will create a table and insert some escape characters in the column value.

Create table #temp

(Name varchar(30))

Insert into #temp values (‘as/ ”””’)

Now let’s run below query and check the results –

SELECT string_escape(name,’json’) FROM #temp

string_escape1.jpg

This function will helps in returning the escape characters from the expression.

Hope you like this post. 🙂

Advertisements

Author:

I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on http://www.sqlservercentral.com/blogs/kapil-blogs/

One thought on “SQL Server – String_Escape function in SQL Server 2016

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s