How to Use Literal Character Strings in SQL SELECT Statement for MySQL

Using Literal Character Strings

A literal string is a sequence of bytes or characters, enclosed within either two single quotes (' ') or two double quotes (" ").

Read Prerequisites for this tutorial and practices if you haven't done so.

Consider the following facts when using literal strings in a SELECT statement:

Literal strings are enclosed in single or double quotation marks.
You can use literal strings just like you normally use a column name in the SELECT statement. The literal string will be displayed in very row of the query result.
Literal strings can be concatenated with another literal string or another column by using function CONCAT.
Special characters (e.g. single or double quotes) in the literal string need to be escaped.

Practice #1: Using a literal string in SELECT statement.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- Use single quotes around literal string
SELECT CategoryID, CategoryName, 'Northwind Category' AS Note
FROM categories;
 
-- Use double quotes around literal string
SELECT CategoryID, CategoryName, "Northwind Category" AS Note
FROM categories;

The two queries above produce the same result set. You can use two single quotes or two double quotes but you can't use one single quote and one double quote for a literal string.

Query result set - 8 rows returned:
Literal string in SELECT statement

Practice #2: Concatenate literal strings with columns in SELECT statement.

-- Concatenate strings and columns
SELECT CONCAT('Quantity Per Unit for ', ProductName, ' is ', QuantityPerUnit)
        AS "Product Details"
FROM products;

The query above uses function CONCAT to concatenate literal string with column data. MySQL functions are covered in Using Single-Row Functions section.

Query result set - 77 rows returned:
Literal strings are concatenated in SELECT statement

Practice #3: Escape single quote character by backward slash.

-- Escape single quote character
SELECT CategoryName, 'Northwind\'s category name' AS Note
FROM categories

Within a string, certain characters have special meaning. Each of these characters needs to be preceded by a backslash \, known as the escape character.

The query above uses a single quote character inside the literal string. Because the literal string is enclosed in two single quotes, we need to escape the one inside the string by using escape character backslash \.

Query result set - 8 rows returned:
Escape single quote in literal string

Practice #4: Use two single quote characters instead of escaping.

Alternatively, you can use two single quote characters to mimic the effect of escaping one single quote character.

-- Use two single quote characters to mimic the effect of 
-- escaping one single quote character.
SELECT CategoryName, 'Northwind''s category name' AS Note
FROM categories;

The query above uses two single quotes and produced the same result as the one using escape character in Practice #3.

Query result set - 8 rows returned:
Escape single quote in literal string

Practice #5: Escape double quote character by backward slash.

-- Escape double quote character
SELECT CategoryName, "Northwind \"category\" name" AS Note
FROM categories;

The double quote character inside the literal string needs to be escaped because the literal string is enclosed in two double quotes.

Query result set - 8 rows returned:
Escape double quote in literal string

Practice #6: Use two double quote characters instead of escaping.

-- Use two double quote characters to mimic the effect of 
-- escaping one double quote.
SELECT CategoryName, "Northwind ""category"" name" AS Note
FROM categories;

The query above uses two double quote characters and produced the same result as the one using escape character in Practice #5.

Query result set - 8 rows returned:
Escape double quote in literal string

Practice #7: One or more single quote characters inside a literal string quoted with two double quotes needs no special treatment and need not to be doubled or escaped.

-- A single quote inside a literal string quoted with two double 
-- quotes needs no special treatment and need not to be doubled or escaped.
SELECT CategoryName, "Northwind category's name" AS Note
FROM categories;

The query above uses a single quote inside the literal string that is quoted with two double quotes. The single quote does not need to be escaped.

Query result set - 8 rows returned:
Enclose single quote in double quoted literal string

Practice #8: One or more double quote characters inside a literal string quoted with two single quote characters needs no special treatment and need not to be doubled or escaped.

-- double quote characters inside a literal string quoted 
-- with two single quote characters needs no special treatment 
-- and need not to be doubled or escaped.
SELECT CategoryName, 'Northwind "category" name' AS Note
FROM categories;

The query above uses double quotes inside the literal string that is quoted with two single quotes. The double quotes do not need to be escaped.

Query result set - 8 rows returned:
Enclose double quotes in single quoted literal string

Practice #9: Escape backslash itself.

-- Add a backslash in the literal string
SELECT CategoryName, 'Northwind \\ category name' AS Note
FROM categories;

The query above displays a backslash in the result. The backslash needs to be escaped by preceding it with another backslash.

Query result set - 8 rows returned:
Escape backslash in literal string

Practice #10: Escape other special characters such as newline character.

-- Add line break in the result set. Line break is achieved by using
-- newline character escape sequence \n
SELECT CategoryName, 'Northwind \ncategory \nname' AS Note
FROM categories;

The query above adds a newline in the result set. Newline character is added in by \n in the literal string.

Below is the query result in text view. We switched the result view from grid to text in order to see the multiple lines in the result. Grid view only shows the first line. To switch to text view in SQLyog, highlight anywhere in the result, and then press Ctrl+L on your keyboard.

Query result in text view:
Escape newline character in query result

To see a list of escaped characters in MySQL, click here to go to MySQL online documentation about String data type.

Other tutorials in this category

1. The Basic SELECT Statement

2. Using Column Alias in SELECT Statement

3. Perform Arithmetic Operations

4. Use WHERE Clause to Conditionally Select Rows

5. Sorting Data

6. Using LIMIT Clause to Constrain the Number of Rows Retrieved

7. Using Comparison Operators, Part I

8. Using Comparison Operators, Part II

9. Using LIKE Comparison Operator

10. Using Logical Operators

11. Eliminating Duplicate Rows

Back to Tutorial Index Page

	Custom Search