Re: [sqlite] Single or double quotes when defining alias?
Ok, thanks for everone's answer. - Original Message - From: James K. Lowden To: sqlite-users@mailinglists.sqlite.org Sent: Monday, October 28, 2019, 18:27:06 Subject: [sqlite] Single or double quotes when defining alias? On Fri, 25 Oct 2019 23:55:20 +0200 Thomas Kurz wrote: > SELECT column1 AS 'c' > --or-- > SELECT column2 AS "d" > On the one hand, the name refers to a column or table identifier. The SQL-92 standard refers to that kind of name as a "correlation name", and its BNF grammar designates a correlation name as a kind of indentifier. Therefore, syntactically, "d" is correct because double-quotes are used to quote identifiers. Which was news to me. I've always used single-quotes for correlation names (on creation, never reference). Not because they need quoting. I never choose a correlation name that needs to be quoted; normally they're just 3 lower-case letters, at most. I would quote them only to make them stand out for the syntax highlighter. And who doesn't like pretty SQL? --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Single or double quotes when defining alias?
On Fri, 25 Oct 2019 23:55:20 +0200 Thomas Kurz wrote: > SELECT column1 AS 'c' > --or-- > SELECT column2 AS "d" > > On the one hand, the name refers to a column or table identifier. The SQL-92 standard refers to that kind of name as a "correlation name", and its BNF grammar designates a correlation name as a kind of indentifier. Therefore, syntactically, "d" is correct because double-quotes are used to quote identifiers. Which was news to me. I've always used single-quotes for correlation names (on creation, never reference). Not because they need quoting. I never choose a correlation name that needs to be quoted; normally they're just 3 lower-case letters, at most. I would quote them only to make them stand out for the syntax highlighter. And who doesn't like pretty SQL? --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Single or double quotes when defining alias?
On 26 Oct 2019, at 4:44am, Keith Medcalf wrote: > If the identifier is also a keyword and used in a location where it could be > that keyword Actually, any location. A SQL parser may pick out a SQL keyword even if it's in the wrong place in the wrong kind of SQL statement. And then issue a syntax error. You have a good point. So the options are these: A) quote all your entity names B) avoid using keywords in your entity names My personal style leans to one of these, but I suppose someone else's may lean to the other. (Obligatory note: although double quotes (undirected speech marks) are preferred, SQLite also understands square brackets and backward apostrophes.) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Single or double quotes when defining alias?
On Friday, 25 October, 2019 20:45, Simon Slavin wrote: >On 25 Oct 2019, at 10:55pm, Thomas Kurz wrote: >> SELECT column2 AS "d" >If you want to do it, do it like that. Double quotes indicate an entity >name. Single quotes indicate a string of characters. >However, almost nobody quotes entity names these days. The language is >written so that you don't need to. Anything unquoted is understood to be >an entity name until proven otherwise. If the identifier is also a keyword and used in a location where it could be that keyword (or any location even where the interpretation as a keyword rather than an identifier would be absurd and you are using a particularly stupid parser); it starts with an ill-conceived character for an identifier; or, it contains an embedded ill-conceived character, then you need to quote the identifier. Ill-conceived starting characters include most non-alphabetic characters except and underscore, and ill-conceived embedded characters include symbols that have other, usually terminal, meanings (space +-*/. etc). Other than those cases you need not quote identifiers. Identifiers are case preserving but case insensitive. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Single or double quotes when defining alias?
On 25 Oct 2019, at 10:55pm, Thomas Kurz wrote: > SELECT column2 AS "d" If you want to do it, do it like that. Double quotes indicate an entity name. Single quotes indicate a string of characters. However, almost nobody quotes entity names these days. The language is written so that you don't need to. Anything unquoted is understood to be an entity name until proven otherwise. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Single or double quotes when defining alias?
Dne 25. 10. 19 v 23:55 Thomas Kurz napsal(a) > this might be a stupid question, but do I have to use single or double quotes > when defining an alias? > > SELECT column1 AS 'c' > --or-- > SELECT column2 AS "d" > > On the one hand, the name refers to a column or table identifier. On the > other hand, at the time of using this statement, the identifier does not > exist yet. At that moment, it is a string literal telling that an identifier > with that name should be created. > > So which one is correct? I was bit surprised that sqlite accepts also AS 'c' (other DBMS do not). But the standard way is AS "d" – it is an identifier. It is like if you are defining a variable in a programming language – the variable with this name does not exist yet, but same rules apply for the name syntax. Franta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Single or double quotes when defining alias?
Dear all, this might be a stupid question, but do I have to use single or double quotes when defining an alias? SELECT column1 AS 'c' --or-- SELECT column2 AS "d" On the one hand, the name refers to a column or table identifier. On the other hand, at the time of using this statement, the identifier does not exist yet. At that moment, it is a string literal telling that an identifier with that name should be created. So which one is correct? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
