How do I use a function result as my alias?

Question

I am wondering if it is possible to use an SQL function to name the column of a query. As a simple example:

SELECT id AS SUBSTR('xAlias', 2) FROM foo

should theoretically return

Alias
------
...
results
...

If we can, which databases would this be available for? If not, then why?

Answer 1

AFAIK, there is no SQL way in any major DBMS product to support this.

To name PIVOT columns in Oracle, for name in.. as seems like the best option.
Apart from that, it's over to dynamic SQL.

---

Original answer below

To name the column of a query, the ANSI standard is simply to add the new name after the column (derived or base) prepended by "AS":

SELECT id, field1, field2 * qty FROM foo

All columns renamed below:

SELECT id AS RenamedID, field1 AS Col1, field2 * qty AS ExtendedTotal FROM foo

This standard works for pretty much all major database systems.
There are some vendor specific variations, such as SQL Server allowing the equal sign

SELECT RenamedID=id FROM foo

And most DBMS allow the omission of the "AS" keyword

SELECT id RenamedID FROM foo

Answer 2

That's a weird requirement and I think no DBMS would support that. I did the test anyway and here are the results:

This doesn't work in SQL Server 2008, MySQL 5 nor PostgreSQL 9 (they share the same sintax):

SELECT id AS left('xAlias', 2) FROM table

In Oracle 11g and SQLite 3 this failed too:

SELECT id AS substr('xAlias', 1, 2) FROM table

I bet you won't find any that supports that :P