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. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.
SELECT CategoryID, CategoryName, 'Northwind Category' AS Note
FROM categories;
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.
In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.
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:
Practice #3: Escape single quote character by backward slash.
In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.
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:
Practice #4: Use two single quote characters instead of escaping.
In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.
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.
In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.
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:
Practice #6: Use two double quote characters instead of escaping.
In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.
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:
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.
In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.
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:
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.
In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.
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:
Practice #9: Escape backslash itself.
In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.
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:
Practice #10: Escape other special characters such as newline character.
In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.
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:
To see a list of escaped characters in MySQL, click here to go to MySQL online documentation about String data type.
