How to use function result as my alias?

Question

I am wondering if the SQL function can be used to indicate a query column. As a simple example:

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

should theoretically come back

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

If possible, which databases will be available for this? If not, why?

Answer 1

AFAIK, there is no SQL method in any large DBMS product.

To name PIVOT columns in Oracle, for name in.. as seems like the best option.
In addition, he moves on to dynamic SQL.

---

Original answer below

To name a query column, the ANSI standard simply adds a new name after the column (derived or base) added 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 in almost all major database systems. There are some vendor-specific options, such as SQL Server, that allow the equal sign

 SELECT RenamedID=id FROM foo 

And most DBMSs allow you to exclude the keyword "AS"

 SELECT id RenamedID FROM foo 

Answer 2

This is a strange requirement, and I think that no DBMS would support it. I ran the test anyway, and here are the results:

This does not work in SQL Server 2008, MySQL 5 and PostgreSQL 9 (they have the same sintax):

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

In Oracle 11g and SQLite 3, this also failed:

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

I bet you will not find anyone supporting this: P

Answer 3

You can use dynamic SQL and generate the query this way, but I don't think there is a way to do this without building strings. I would suggest that all major DBMSs will support dynamic SQL.