EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial Escape Character SQL

Escape Character SQL

Updated May 15, 2023

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 –

Escape Character SQL output 1

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 –

Escape Character SQL output 2

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 –

Escape Character SQL output 3

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 –

Escape Character SQL output 4

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 –

output 5

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 –

output 6

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 –

output 7

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

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

SQL output 8

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 –

output 9

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

We hope that this EDUCBA information on “Escape Character SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL sum()
  2. SQL NTILE()
  3. SQL ROW_NUMBER
  4. SQL Rename Table
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
 204+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more