String Literals

Introduction

Literals or string literals are the way to define or express some constant or non-changing value in MySQL. The terms constant value or literal reference to the same meaning, i.e. a fixed value. You might be familiar with the declaration of the variables with a constant value. Similarly a literal is used while declaring a constant variable. For example, JavaScript, SQL are all character or string literals.

A string literal is a sequence of characters enclosed in single quotes('') or double quotes(""). For example, 'Hello World' is a string literal and can be written as "Hello World" or 'Hello World'.

Let's take an example to understand how you can create a string literal in MySQL. Consider a table named Students as a reference table for the examples mentioned in this article.

Students:

Consider you want to get the marks of students in the subject of maths only. To achieve this, the below query is executed on the above-given Students table.

The above query can also be written as:

As shown in the above example, the string literal examples contain a string enclosed in quotes. The above queries will give the same result either you use single quotes or double quotes.

The result set of the above query is shown below:

But what if you want to include a single quote within your string literal? Consider another example which requires single quotes inside a string literal.

'You're studying String Literals.'

As shown in the above example, the string literal contains a single quote in the word. This additional single quote will cause the literal to terminate at the You' quote. And this will lead to some errors. If you want to add the quotes in the string literals, then you need to escape the single quote with an additional single quote. This process is called as Escaping* which is covered in the next section.

Escape Sequences

If you want to use some special characters that cannot be typed or entered manually inside the string literals, then they must be used with the escape sequences. Examples of the special characters include a new-line character, backslash character, a hexadecimal byte value, etc.

Some of the special characters are mentioned in the table below.

Example:

As this example was causing errors because of the presence of single quote inside a string literal. To resolve this, you need to escape the single quote with an additional single quote, as shown below:

This can also be done in the other way as shown below:

You can escape the special characters with the backslash also. Each of the special character inside the string literals can begin with the backslash character (\) which is also known as escape character. For any of the escape sequences in the string literal, the backslash is ignored i.e. escape character is interpreted as if it was not escaped. For example,

• \' interpreted as '.
• \t interpreted as tab as shown in the above table.
• \T interpreted as T. Because these sequences are case-sensitive.

Ways to Include Quote Characters Within a String

There are several ways to include the quote characters within a string. There arises some cases when you write SQL queries that need to include quotes within a constant value in the query. To achieve this there are ways which are mentioned below:

• If you want to include a single quote within a string literal('') then this can be achieved by using ' inside the single quote string literal with ', i.e. escape a single quote using a single quote.

  For Example:

  Output:

• If there is a need to include " inside the string literal, then this can be achieved by using " inside the double-quoted string literal with " i.e. escape the double quote using a double quote.

  For Example:

  Output:

• A ' inside a string literal "" needs no special treatment, and this condition need not be escaped. In the same way, " inside a string quoted with ' need not be escaped.

  For Example:

  Output:

  For Example:

  Output:

• You can include special characters by preceding the quote character with an escape character, i.e. backslash ( \ ).

  Example:

  Output:

How Quoting and Escaping Work?

While writing SQL queries, any strings that might contain any special characters like single quotes, double quotes, etc. must be properly escaped before the string is used as a constant value in a SQL statement. You can achieve this in two ways.

• You can process the string with the function that escapes the special characters. You can use mysql_real_escape_string_quote() C API function to escape the characters. This function creates the legal SQL string for use in a SQL statement. You can also use the quote() function within SQL statements that construct other SQL statements. The Perl DBI interface provides a way to convert special characters to proper escape sequences using the quote method. However, this is the explicit way to escape the special characters in the string. You do not need to know about this method as of now as this is beyond the scope of this article.

• An alternative of explicitly escaping special characters is widely used. Many SQL APIs provide placeholder capability that enables you to insert special characters or markers into a string. In this case, the API takes care of escaping special characters in the string values. And this method of escaping the special characters is covered in this article.