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:

1. Literal strings are enclosed in single or double quotation marks.

2. 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.

3. Literal strings can be concatenated with another literal string or another column by using function CONCAT.

4. 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.

-- 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:

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

Query below concatenates strings and columns to form a sentence. It uses function CONCAT to concatenate literal string with column data. MySQL functions are covered in Using Single-Row Functions section.

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

Query result set - 77 rows returned:

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

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 below 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 \.

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

Query result set - 8 rows returned:

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

This query uses two single quote characters to mimic the effect of escaping one single quote character.

Alternatively, you can 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:

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

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

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

Query result set - 8 rows returned:

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

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

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

Query result set - 8 rows returned:

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.

The query below uses a single quote inside the literal string that is quoted with two double quotes. The single quote does not need to be 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;

Query result set - 8 rows returned:

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.

The query below uses double quotes inside the literal string that is quoted with two single quotes. The double quotes do not need to be 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;

Query result set - 8 rows returned:

Practice #9: Escape backslash itself.

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

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

Query result set - 8 rows returned:

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

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

-- 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;

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:

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