INF: QUOTED_IDENTIFIER and Strings with Single Quotation Marks

• Microsoft SQL Server, version 6.0
• Microsoft Open Database Connectivity driver, version 2.5

SUMMARY

SQL Server 6.0 introduced support for the ANSI SQL rules regarding the use of single and double quotation marks. This article describes how to process single and double quotation marks in an ANSI environment.

MORE INFORMATION

Starting with SQL Server 6.0, you can use SET QUOTED_IDENTIFIER ON to have SQL Server start enforcing the ANSI SQL rules regarding quotation marks. According to the ANSI rules, double quotation marks are reserved for delimiting identifiers (such as database, table, or column names), while single quotation marks are reserved for delimiting character strings being used as data. For example, consider the following statement:

   select * from "authors" where "au_lname" = 'White'

'White' in double quotation marks. ANSI SQL interprets "White" as an

According to ANSI SQL, when a character string includes a single quotation mark or an apostrophe, it should be represented by two single quotes, as in the following example:

   select * from authors where au_lname = 'O''Brien'

ODBC can be used in a way that eliminates the need to first parse character strings for single quotation marks. When an ODBC application uses SQLBindParameter() to bind character program variables to parameter markers in SQL commands, the character strings are not enclosed in either single or double quotation marks. There is no need for the application to have to parse the strings for single quotation marks. The following is an example:

   UCHAR *szCol1[41]="";
   SDWORD pcbVal1 = SQL_NTS;

   SQLPrepare(hstmt,
          (UCHAR *)"select * from authors where au_lname = ?",
          SQL_NTS);
   SQLBindParameter(hstmt1,
                 1,
                 SQL_PARAM_INPUT,
                 SQL_C_CHAR,
                 SQL_CHAR,
                 40,
                 0,
                 szCol1,
                 sizeof(szCol1),
                 &pcbVal1);
   strcpy(szCol1, "O'Brien");
   SQLExecute(hstmt1);