Single and Double Quotes - Informix to SQL Server Migration

In Informix, by default you can simultaneously use both single '' and double "" quotes for the owner names and string constants (literals), but you cannot use them for other identifiers such as table and column names.

In SQL Server, by default you can use double "" quotes and [] for all identifiers, and only single '' quotes for string constants.

Informix:

  -- Using "" for owner name
  CREATE TABLE "informix".colors (name VARCHAR(30));
  /* Table created. */
 
   -- Using '' for owner name
  CREATE TABLE 'informix'.colors2 (name VARCHAR(30));
  /* Table created. */
 
  -- Using single and double quotes for owner name and string values  
  INSERT INTO "informix".colors VALUES ("Green");
  /*  1 row(s) inserted. */
  INSERT INTO "informix".colors VALUES ('Orange');
  /*  1 row(s) inserted. */
  INSERT INTO 'informix'.colors VALUES ("Blue");
  /* 1 row(s) inserted. */

But you cannot use double quotes "" for table and column names by default:

Informix:

  CREATE TABLE informix."colors3" (name VARCHAR(30));
  /* 201: A syntax error has occurred. */
  CREATE TABLE informix.colors3 ("name" VARCHAR(30));
  /* 201: A syntax error has occurred. */

In Informix, you can set the environment variable DELIMIDENT (not set by default) to enable double "" quotes for identifiers, and disable them for string literals:

Informix:

  -- setenv DELIMIDENT on Linux
  -- set DELIMIDENT=y on Windows 
 
  -- We can create table now 
  CREATE TABLE informix."colors3" ("name" VARCHAR(30));
  /* Table created. */
 
  INSERT INTO informix."colors3" VALUES ('Green');
  /* 1 row(s) inserted. */
 
  -- Double quotes are not allowed for string literals anymore
  INSERT INTO informix."colors3" VALUES ("Red");
  /* 201: A syntax error has occurred. */

SQL Server:

  -- You can use "" and [] for any identifier
  CREATE TABLE "dbo".[colors] ("name" VARCHAR(30));
  /* Commands completed successfully. */
 
  INSERT INTO colors VALUES ('Green');
  /* (1 row affected) */
 
  -- But double quotes are not allowed for string literals by default
  INSERT INTO colors VALUES ("Orange");
  /* Msg 207, Level 16, State 1, Line 1 */
  /* Invalid column name 'Orange'. */

In SQL Server, you can set the QUOTED_IDENTIFIER OFF option to disable double "" quotes for identifiers, and enable them for string literals:

SQL Server:

  SET QUOTED_IDENTIFIER OFF
 
  -- Now we can use double quotes for string literals
  INSERT INTO colors VALUES ("Orange");
  /* (1 row affected) */
 
  -- Single quotes are also still allowed for string literals
  INSERT INTO colors VALUES ('Blue');
  /* (1 row affected) */
 
  -- But you cannot use double quotes for identifiers anymore
  INSERT INTO "colors" VALUES ('Red');
  /* Msg 102, Level 15, State 1, Line 1 */
  /* Incorrect syntax near 'colors'. */

For more information, see Informix to SQL Server Migration.