SQL - STRING_ESCAPE() Function

---

---

The SQL STRING_ESCAPE() function is used to convert the escape's special characters into the string.

It accepts two parameters text and type. It returns a new string(text) with an escaped characters. Currently, the STRING_ESCAPE() function only supports escaping JOSN’s special characters.

An Escape character is a character that invokes an alternative interpretation of the following characters in a character sequence. For example, the backslash(\) is the escape character. You can use the escape character in SQL to search for the special character and to escape the special character, you can use the backslash(\) before it.

Following is the syntax of the SQL STRING_ESCAPE() function −

STRING_ESCAPE( text , type )   

Parameters

• text − It Is a nvarchar expression expression representing the object that should be escaped.

• type − It is an escaping rule that will be applied.

Return value

It returns a text with escaped characters.

Example

In the following example,we are using the SQL STRING_ESCAPE() function to convert the escape special character '\HELLO\WORLD' into the string.

SELECT STRING_ESCAPE('\HELLO\WORLD', 'JSON') as New_string;

Output

On executing the above program, it will produce the following output −

+----------------+
| New_string     |
+----------------+
| \\HELLO\\WORLD |
+----------------+

Example

Following is another example of the SQL STRING_ESCAPE() function, here, we are trying to convert or escapes the special escape character from the text 'https://www.tutorialspoint.com/users'into the text. This function will returns text with escape characters.

SELECT STRING_ESCAPE('https://www.tutorialspoint.com/users', 'JSON') as New_string;

Output

+-----------------------------------------+
| New_string                              |
+-----------------------------------------+
| https:\/\/www.tutorialspoint.com\/users |
+-----------------------------------------+

Example

If we pass the type argument value as empty to the STRING_ESCAPE() function, this function throws an invalid value specified for argument 2 error.

SELECT STRING_ESCAPE('\\TutorialsPount\easylearning', '') as New_string;

Output

Following is the output of the above statement −

An invalid value was specified for argument 2.

Example

If we pass the type argument as NULL to this function, the STRING_ESCAPE() function throws an invalid datatype error.

SELECT STRING_ESCAPE('\\TutorialsPount\easylearning', NULL) as New_string;

Output

After executing the above program, it produces the following output −

Argument data type NULL is invalid for argument 2 of string_escape function.

Example

You can pass the table column as an argument to the STRING_ESCAPE() function to convert the column escape character into a string with an escape character. Assume, we have created a table with the name Customers using the CREATE statement as follows −

CREATE TABLE CUSTOMERS(    
   ID INT NOT NULL,    
   FIRST_NAME VARCHAR (20),
   LAST_NAME VARCHAR(20),s
   AGE INT NOT NULL,    
   ADDRESS CHAR (25) ,    
   SALARY DECIMAL (18, 2));

Let’s insert some records into the Customers table with escape characters using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES (1, '/Rohan\','Verma', 33, 'Hyderbad', 2100.00 ); 
INSERT INTO CUSTOMERS VALUES (2, '\\Kamlesh','Kumar', 30, 'Lucknow', 2500.00 ); 
INSERT INTO CUSTOMERS VALUES (3, 'Seeta\\','Sharma', 23, 'Delhi', 3150.00 );

Following is the SQL query to convert the escape character of the column FIRST_NAME into the string with an escape character in the Customer table −

SELECT ID, FIRST_NAME, STRING_ESCAPE(FIRST_NAME, 'JSON') AS NEW_LIST FROM CUSTOMERS;

Output

Following is the output of the above query −

+--------------------+-----------------+
| ID  | FIRST_NAME   | NEW_LIST        |
+--------------------+-----------------+
| 1   | /Rohan\      |  \/Rohan\\      |
| 2   | \\Kamlesh    |  \\\\Kamlesh    |
| 3   |  Seeta\\     |  Seeta\\\\      |
+--------------------+-----------------+