Mike McGonagle wrote: > > Could you site a reference on this? I have been looking for documentation on > how SQL deals with quoted things for a while now, but have not found > anything. >
Mike, Standard SQL is defined by several different standards; SQL-92, SQL:1999, and SQL:2003. Ultimately they define how quoting is supposed to be done. In standard SQL the single quote character is used to enclose a literal string. The double quote character is used to enclose an identifier (table or column name for example) that may contain special characters like a space that could confuse the parser. SQLite uses this basic scheme as described at http://www.sqlite.org/lang_expr.html. From that page: > A string constant is formed by enclosing the string in single quotes ('). A > single quote within the string can be encoded by putting two single quotes in > a row - as in Pascal. C-style escapes using the backslash character are not > supported because they are not standard SQL. SQLite also has several extensions to this syntax for better compatibility with other database software that had previously adopted non-standard quoting. It allows square bracket instead of double quotes to enclose identifiers (as used by Microsoft products like Access and MS-SQL Server). I also allows double quotes around literal strings (as used by mySQL). This last case is complicated by the fact that the literal can also be an identifier. If the literal value is a valid identifier and an identifier is legal at that location, it is assumed to be an identifier with standard double quote quoting. These rules are described at http://www.sqlite.org/lang_keywords.html > If you want to use a keyword as a name, you need to quote it. There are three > ways of quoting keywords in SQLite: > > 'keyword' A keyword in single quotes is interpreted as a literal > string if it occurs in a context where a string literal is allowed, otherwise > it is understood as an identifier. > "keyword" A keyword in double-quotes is interpreted as an > identifier if it matches a known identifier. Otherwise it is interpreted as a > string literal. > [keyword] A keyword enclosed in square brackets is always > understood as an identifier. This is not standard SQL. This quoting mechanism > is used by MS Access and SQL Server and is included in SQLite for > compatibility. > This also says that SQLite will accept a single quoted literal as an identifier in certain situations. I'm not aware of any other database that used single quotes that way, but I'm sure there was one somewhere along the line. Aren't standards wonderful, especially when everyone has their own. :-) HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
