Working with Strings - MySQL - page 149

INSERT INTO t SET binary_col = 0 xdeadbeef ;
• To specify a character set for interpretation of a literal string, use an introducer
consisting of a character-set name preceded by an underscore:
_latin1 'abcd'
_ucs2 'abcd'
An introducer tells the server how to interpret the string that follows it. For _lat
in1 'abcd' , the server produces a string consisting of four single-byte characters.
For _ucs2 'abcd' , the server produces a string consisting of two two-byte characters
because ucs2 is a double-byte character set.
To ensure that a string is a binary string or that a nonbinary string has a specific character
set or collation, use the instructions for string conversion given in Recipe 5.5 .
A quoted string that includes the same quote character produces a syntax error:
mysql> SELECT 'I'm asleep';
ERROR 1064 (42000): You have an error in your SQL syntax near 'asleep''
You have several ways to deal with this:
• Enclose a string containing single quotes within double quotes (assuming that
ANSI_QUOTES is disabled), or enclose a string containing double quotes within single
quotes:
mysql> SELECT "I'm asleep", 'He said, "Boo!"';
+------------+-----------------+
| I'm asleep | He said, "Boo!" |
+------------+-----------------+
| I'm asleep | He said, "Boo!" |
+------------+-----------------+
• To include a quote character within a string quoted by the same kind of quote,
double the quote or precede it with a backslash. When MySQL reads the statement,
it strips the extra quote or the backslash:
mysql> SELECT 'I''m asleep', 'I\'m wide awake';
+------------+----------------+
| I'm asleep | I'm wide awake |
+------------+----------------+
| I'm asleep | I'm wide awake |
+------------+----------------+
mysql> SELECT "He said, ""Boo!""", "And I said, \"Yikes!\"";
+-----------------+----------------------+
| He said, "Boo!" | And I said, "Yikes!" |
+-----------------+----------------------+
| He said, "Boo!" | And I said, "Yikes!" |
+-----------------+----------------------+
A backslash turns off any special meaning of the following character, including
itself. To write a literal backslash within a string, double it: