Microsoft KB Archive/156501

= INF: QUOTED_IDENTIFIER and Strings with Single Quotation Marks =

Article ID: 156501

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q156501



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.

The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties. SET QUOTED_IDENTIFIER defaults to OFF for connections from B-Library applications.

Additional query words: embedded

Keywords: kbhowto kbusage KB156501

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.