Single and Double Quotes - Sybase ASE to MariaDB Migration

In Sybase ASE and MariaDB, 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';

MariaDB:

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

MariaDB:

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