Thanks Trey and Dennis, I guess it is just confusing to me because these things appear to be "optional", but actually have very specific rules for how to use them or NOT use them. The only one that seems mandatory are the single quote literals. I try not to use field names with spaces or unusual characters in them, so the double quotes seem to be only optional in my case.
Thanks again. Mike On Feb 8, 2008 9:37 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > 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 > -- Peace may sound simple—one beautiful word— but it requires everything we have, every quality, every strength, every dream, every high ideal. —Yehudi Menuhin (1916–1999), musician _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
