Escape Character SQL

Introduction to Escape Character SQL

Escape Characters in SQL are mostly used in the literal strings to mention them before any character whose interpretation and behavior needs to be changed. The meaning of that character turns out to be different from its usual meaning of that character. The literal strings or the string values that are stored in the columns having a data type of varchar or string are also considered as literals when retrieved or used. These literal strings are nothing but the sequence of the characters that have a definite beginning and end. The start and end of characters are determined by single quotes or double quotes in SQL.

There often arises a need where we want to use single quotes or double quotes inside the string literal in between. If this happens then, those quotes that are part of the value of string can also be interpreted as the delimiter. For this, we can use delimiters. Other than this, many things need to be specified for mentioning format, such as tabs, newlines, etc. Delimiters are also used for these cases. In this article, we will learn about the usage of escape characters in the string literals in SQL with the help of certain examples.

Escaping single and double quotes in the string literals

The string literal values are delimited to mark the beginning and the ending by making the use of single and double-quotes. The occurrence of any of the single quote in between the string value when single quotes are used to mark the beginning and end of the literal or when double quotes are used as delimiters and double-quote is used inside the string literal value then the escape characters are used to specify that those in-between quotes inside the string literals are part of it. The default escape sequence value in SQL is the backslash(\).

Let us consider one example to make the usage of backslash as an escape character. We have one string, ‘K2 is the 2’nd highest mountain in Himalayan ranges!’ that is delimited with the help of single quotes, and the string literal value contains the word 2’nd that has a single quote in itself. Now, we have to retrieve the string value as the output of the select query in SQL. We will use the simple select statement to do so. Consider the following query statement –

SELECT 'K2 is the 2'nd highest mountain IN Himalayan ranges!'

The output of the above query statement gives the following output along with the error saying that the syntax is incorrect –

This is because of the presence of a single quote in between the string value before the semicolon that marks the completion of the query statement –

Let us now try using the escape character backslash before the single quote that occurs in the 2’nd value inside string literal. The escape character backslash will tell that the single quote following it is a part of the string literal and not the delimiter. We will make the use of the following query statement for this –

SELECT 'K2 is the 2\'nd highest mountain in Himalayan ranges!'

The output of the above query statement gives the following result –

As we can observe, the output contains the appropriate string value as expected. Similarly, consider the following statement that contains a double quote to mention the inches of the screw and is enclosed between the double quotes to specify the delimiter for the string –

“We will need a 2″ screw to hang this frame on the above wall.”

The use of this statement in SQL, say for example, SELECT query in the following –

SELECT "We will need a 2" screw to hang this frame on the above wall.";

The output of the above query statement gives the following result –

Now consider the usage escape character before double quote that is mentioned after 2 to mention inches of screw required.

SELECT "We will need a 2\" screw to hang this frame on the above wall.";

The output of the above query statement gives the following result –

Use of escape characters for other usages –

There are multiple usages of escape characters in SQL that help in changing the meaning and interpretation of the characters in SQL query statements. Some of them are as listed below –

Quotation mark (“)                                         –                                               \”

Form feed                                                         –                                               \f

Solidus (/)                                                        –                                               \/

Horizontal tab                                                 –                                               \t

Carriage return                                               –                                               \r

New line                                                           –                                               \n

Backspace                                                        –                                               \b

Reverse solidus (\)                                         –                                               \\

Underscore (_)                                               –                                               \_

Percentile (%)                                                 –                                               \%

Let us consider an existing table named students that has the following structure and contents as shown in the output of the following query statement –

SELECT * FROM students;

The output of the above query statement gives the following result –

We will use like a statement to retrieve all the students with a 90% percentage. For this, let us use the following query statement that contains the escape sequence for percentage sign allowance in the string to be compared –

SELECT * FROM students WHERE percentage LIKE "90\%";

The output of the above query statement gives the following result –

Now, we will use the example in which we have to print the sentence “Good Morning!” on the first line, and after a new line, there should be the greeting sentence “Have a Nice Day!”. We will use the following query statement to define the new line with the help of escape character backslash and ‘n’ character that collectively specify the new line. As the output is shown in row and column format, we will not be able to see a new line. But once you copy and paste the output content on any editor or notepad, it will show you the following output –

SELECT "Good Morning!\nHave a Nice Day!";

The output of the above query statement gives the following result –

Let us take one more example in which we have to get the output having the sentence “Lost in Nature!” after one tab space and the sentence “Experience the wanderlust….”. For this, we will use the following query statement with an escape character to mention tab –

SELECT "Experience the wanderlust....\tLost in Nature!" ;

The output of the above query statement is as shown below –

Conclusion – Escape Character SQL

The escape characters in SQL help us change the meaning of certain characters and their interpretation, often the characters used as wildcard characters, some of which are as discussed in the above examples.

Recommended Articles

This is a guide to Escape Character SQL. Here we discuss the Escaping single and double quotes in the string literals along with the examples. You may also have a look at the following articles to learn more –

1. MySQL sum()
2. SQL NTILE()
3. SQL ROW_NUMBER
4. SQL Rename Table