DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) string-syntax

Info Catalog (mysql.info) literals (mysql.info) literals (mysql.info) number-syntax
 
 9.1.1 Strings
 -------------
 
 A string is a sequence of bytes or characters, enclosed within either
 single quote (‘`''’) or double quote (‘`"'’) characters.
 Examples:
 
      'a string'
      "another string"
 
 If the `ANSI_QUOTES' SQL mode is enabled, string literals can be quoted
 only within single quotes. A string quoted within double quotes is
 interpreted as an identifier.
 
 A binary string is a string of bytes that has no character set or
 collation. A non-binary string is a string of characters that has a
 character set and collation. For both types of strings, comparisons are
 based on the numeric values of the string unit.  For binary strings,
 the unit is the byte. For non-binary strings the unit is the character
 and some character sets allow multi-byte characters.
 
 String literals may have an optional character set introducer and
 `COLLATE' clause:
 
      [_CHARSET_NAME]'STRING' [COLLATE COLLATION_NAME]
 
 Examples:
 
      SELECT _latin1'STRING';
      SELECT _latin1'STRING' COLLATE latin1_danish_ci;
 
 For more information about these forms of string syntax, see 
 charset-literal.
 
 Within a string, certain sequences have special meaning. Each of these
 sequences begins with a backslash (‘`\'’), known as the _escape
 character_. MySQL recognizes the following escape sequences:
 
 `\0'    An ASCII 0 (`NUL') character.
 `\''    A single quote (‘`''’) character.
 `\"'    A double quote (‘`"'’) character.
 `\b'    A backspace character.
 `\n'    A newline (linefeed) character.
 `\r'    A carriage return character.
 `\t'    A tab character.
 `\Z'    ASCII 26 (Control-Z). See note following the table.
 `\\'    A backslash (‘`\'’) character.
 `\%'    A ‘`%'’ character. See note following the table.
 `\_'    A ‘`_'’ character. See note following the table.
 
 These sequences are case sensitive. For example, ‘`\b'’ is
 interpreted as a backspace, but ‘`\B'’ is interpreted as ‘`B'’.
 
 The ASCII 26 character can be encoded as ‘`\Z'’ to enable you to
 work around the problem that ASCII 26 stands for END-OF-FILE on Windows.
 ASCII 26 within a file causes problems if you try to use `mysql DB_NAME
 < FILE_NAME'.
 
 The ‘`\%'’ and ‘`\_'’ sequences are used to search for literal
 instances of ‘`%'’ and ‘`_'’ in pattern-matching contexts where
 they would otherwise be interpreted as wildcard characters. See the
 description of the `LIKE' operator in 
 string-comparison-functions. If you use ‘`\%'’ or ‘`\_'’ in
 non-pattern-matching contexts, they evaluate to the strings ‘`\%'’
 and ‘`\_'’, not to ‘`%'’ and ‘`_'’.
 
 For all other escape sequences, backslash is ignored. That is, the
 escaped character is interpreted as if it was not escaped.  For
 example, ‘`\x'’ is just ‘`x'’.
 
 There are several ways to include quote characters within a string:
 
    * A ‘`''’ inside a string quoted with ‘`''’ may be written as
      ‘`'''’.
 
    * A ‘`"'’ inside a string quoted with ‘`"'’ may be written as
      ‘`""'’.
 
    * Precede the quote character by an escape character (‘`\'’).
 
    * A ‘`''’ inside a string quoted with ‘`"'’ needs no special
      treatment and need not be doubled or escaped. In the same way,
      ‘`"'’ inside a string quoted with ‘`''’ needs no special
      treatment.
 
 The following `SELECT' statements demonstrate how quoting and escaping
 work:
 
      mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
      +-------+---------+-----------+--------+--------+
      | hello | "hello" | ""hello"" | hel'lo | 'hello |
      +-------+---------+-----------+--------+--------+
 
      mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
      +-------+---------+-----------+--------+--------+
      | hello | 'hello' | ''hello'' | hel"lo | "hello |
      +-------+---------+-----------+--------+--------+
 
      mysql> SELECT 'This\nIs\nFour\nLines';
      +--------------------+
      | This
      Is
      Four
      Lines |
      +--------------------+
 
      mysql> SELECT 'disappearing\ backslash';
      +------------------------+
      | disappearing backslash |
      +------------------------+
 
 If you want to insert binary data into a string column (such as a
 `BLOB' column), the following characters must be represented by escape
 sequences:
 
 `NUL'   `NUL' byte (ASCII 0). Represent this character by ‘`\0'’ (a
         backslash followed by an ASCII ‘`0'’ character).
 `\'     Backslash (ASCII 92). Represent this character by ‘`\\'’.
 `''     Single quote (ASCII 39). Represent this character by ‘`\''’.
 `"'     Double quote (ASCII 34). Represent this character by ‘`\"'’.
 
 When writing application programs, any string that might contain any of
 these special characters must be properly escaped before the string is
 used as a data value in an SQL statement that is sent to the MySQL
 server. You can do this in two ways:
 
    * Process the string with a function that escapes the special
      characters. In a C program, you can use the
      `mysql_real_escape_string()' C API function to escape characters.
      See  mysql-real-escape-string. The Perl DBI interface
      provides a `quote' method to convert special characters to the
      proper escape sequences.  See  perl. Other language
      interfaces may provide a similar capability.
 
    * As an alternative to explicitly escaping special characters, many
      MySQL APIs provide a placeholder capability that enables you to
      insert special markers into a statement string, and then bind data
      values to them when you issue the statement. In this case, the API
      takes care of escaping special characters in the values for you.
 
Info Catalog (mysql.info) literals (mysql.info) literals (mysql.info) number-syntax
automatically generated byinfo2html