Sql: Using special characters as alias for table columns?

Question

Can I use any special character as alias name for my table column.

for e.g.: select id as #,first_name,last_name from student;

Answer 1

You would have to use a quoted identifier:

select id as "#",first_name,last_name from student

You are allowed a # in an unquoted object name (which includes aliases), from object naming rule 7:

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.

Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

But not as a single character name, because of rule 6:

Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

Answer 2

You could use quoted-idetifier i.e. double-quotation marks around the alias.

From the docs,

Database Object Naming Rules

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

• A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

• A nonquoted identifier is not surrounded by any punctuation.

For example,

SQL> SELECT empno as "#" FROM emp WHERE ROWNUM <=5;

         #
----------
      7369
      7499
      7521
      7566
      7654

SQL>

Alternatively, in SQL*Plus you could use the HEADING command.

For example,

SQL> column empno heading #
SQL> SELECT empno FROM emp WHERE ROWNUM <=5;

         #
----------
      7369
      7499
      7521
      7566
      7654

SQL>