Connections, Character Sets and Strings - 5.4 Writing String Literals

Problem

You need to write literal strings in SQL statements.

Solution

Learn the syntax rules that govern string values.

Discussion

Strings can be written several ways:

• Enclose the text of the string within single or double quotes:
  
    'my string'
    "my string"
  
  Be aware that you cannot use double quotes for quoting strings when the ANSI_QUOTES SQL mode is enabled. With that mode enabled, the server interprets double quote as the quoting character for identifiers such as table or column names, and not for strings. (See Recipe 2.6.) For this reason, it’s preferable to adopt the convention of always writing quoted strings using single quotes. That way, MySQL will interpret them as strings and not as identifiers regardless of the ANSI_QUOTES setting. 
  
• Use hexadecimal notation. Each pair of hex digits produces one byte of the string. abcd can be written using any of these formats: 
  
    0x61626364
    X'61626364'
    x'61626364'
  
  MySQL treats strings written using hex notation as binary strings. Not coincidentally, it’s common for applications to use hex strings when constructing SQL statements that refer to binary values:
  
    INSERT INTO t SET binary_col = 0xdeadbeef; 
  
• 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 following string. For _latin1 '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.6.

If you need to write a quoted string that includes a quote character and you put the quote into the string as is, a syntax error results:

  mysql> SELECT 'I'm asleep';
  ERROR 1064 (42000): You have an error in your SQL syntax near 'asleep''

There are several ways to deal with this:

• Enclose a string containing single quotes within double quotes (assuming that ANSI_QUOTES is disabled):
  
    mysql> SELECT "I'm asleep";
    +------------+
    | I'm asleep   |
    +--------------+
    | I'm asleep   |
    +--------------+
  
  The converse also works; a string containing double quotes can be enclosed within single quotes:
  
    mysql> SELECT 'He said, "Boo!"';
  
    +-----------------+
    |He said, "Boo!"     |
    +-----------------+
    | He said, "Boo!"    |
    +--------------------+ 
  
• To include a quote character within a string that is quoted by the same kind of quote, either double the quote or precede it by a backslash. When MySQL reads the statement, it will strip off 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. (It causes a temporary escape from normal string processing rules, so sequences such as \' and " are called escape sequences.) This means that backslash itself is special. To write a literal backslash within a string, you must double it:
  
    mysql> SELECT 'Install MySQL in C:\\mysql on Windows'; 

   

  Install MySQL in C:\mysql on Windows

  Install MySQL in C:\mysql on Windows

  
  Other escape sequences recognized by MySQL are \b (backspace), \n (newline, also called linefeed), \r (carriage return), \t (tab), and \0 (ASCII NUL). 
  

   

•  Write the string as a hex value:
  
    mysql> SELECT 0x49276D2061736C656570;
  
    +------------------------+
    | 0x49276D2061736C656570    |
    +---------------------------+
    | I'm asleep                |
    +---------------------------+

See Also

If you are issuing SQL statements from within a program, you can refer to strings or binary values symbolically and let your programming interface take care of quoting: use the placeholder mechanism provided by the language’s database-access API. See Recipe 2.5 for details. Alternatively, load binary values such as images from files using the LOAD_FILE() function; Recipe 18.6 shows an example.

If you are issuing SQL statements from within a program, you can refer to strings or binary values symbolically and let your programming interface take care of quoting: use the placeholder mechanism provided by the language’s database-access API. See the LOAD_FILE( )

Next: 5.5 Checking a String’s Character Set or Collation >>
 

More Database Articles Articles
More By O'Reilly Media

Comments On: Connections, Character Sets and Strings

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Check it out today at your favorite bookstore. Buy this book now.