INF: QUOTED_IDENTIFIER and Strings with Single Quotation Marks

ID: Q156501

The information in this article applies to:

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'

With this option enabled, it is not legal to enclose the character string 'White' in double quotation marks. ANSI SQL interprets "White" as an identifier, not a character string.

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'

This means that applications must parse character strings for single quotation marks before building the strings into SQL commands, and add a second single quotation mark.

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);

Note that while the strcpy command contains the string "O'Brien," C only puts the characters O'Brien in the variable szCol1. The double quotation marks in this case are the string delimiters for C, and have nothing to do with SQL.

Additional query words: embedded 


Keywords          : kbusage SSrvGen 
Version           : 2.5 6.0
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 2, 1999