Single and Double Quotes - Sybase ASE to MySQL Migration

In Sybase ASE and MySQL, by default you can simultaneously use both single '' and double "" quotes for string literals.

Sybase ASE:

  -- Use single and double quotes for strings
  SELECT "abc", 'abc';

MySQL:

  -- Use single and double quotes for strings
  SELECT "abc", 'abc';

Both queries return:

In Sybase ASE, the QUOTED_IDENTIFIER option is set to OFF by default, so "" double quotes can only be used for string literals (not identifiers).

If you set QUOTED_IDENTIFIER ON, then you can use "" double quotes for identifiers only:

Sybase ASE:

  SET QUOTED_IDENTIFIER ON
 
  -- Double quotes cannot be used for strings anymore
  SELECT "abc";
  /* Error: Invalid column name 'abc'. */

In MySQL, the ANSI_QUOTES option is not set by default, so "" double quotes can only be used for string literals (not identifiers).

If you set ANSI_QUOTES, then you can use "" double quotes for identifiers only:

MySQL:

  SET sql_mode = ANSI_QUOTES;
 
  -- Double quotes cannot be used for strings anymore
  SELECT "abc";
  /* ERROR 1054 (42S22): Unknown column 'abc' in 'field list' */

For more information, see Sybase ASE to MySQL Migration.