Lalit Mohan's: 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. 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.

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

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

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:

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:

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:

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:

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:

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:

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:

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:

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

Lalit Mohan's

Tuesday, April 19, 2011

Using Literal Character Strings

No comments: