CODEWITHSUNDEEP
sql
Roman
Roman

Reputation: 176

How do I use a function result as my alias?

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?

Upvotes: 5

Views: 5922

Answers (3)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

You could use dynamic SQL and generate the query in that manner, but I don't think there is a way to do that without string building. I would imagine all major DBMS's would support dynamic SQL.

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

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

Upvotes: 3

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

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

Upvotes: 3

Related Questions