Single or double quotes when defining alias?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Single or double quotes when defining alias?

Thomas Kurz
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Single or double quotes when defining alias?

František Kučera
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Single or double quotes when defining alias?

Simon Slavin-3
In reply to this post by Thomas Kurz
On 25 Oct 2019, at 10:55pm, Thomas Kurz <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Single or double quotes when defining alias?

Keith Medcalf

On Friday, 25 October, 2019 20:45, Simon Slavin <[hidden email]> wrote:

>On 25 Oct 2019, at 10:55pm, Thomas Kurz <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Single or double quotes when defining alias?

Simon Slavin-3
On 26 Oct 2019, at 4:44am, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Single or double quotes when defining alias?

James K. Lowden
In reply to this post by Thomas Kurz
On Fri, 25 Oct 2019 23:55:20 +0200
Thomas Kurz <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Single or double quotes when defining alias?

Thomas Kurz
Ok, thanks for everone's answer.


----- Original Message -----
From: James K. Lowden <[hidden email]>
To: [hidden email] <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users