CODEWITHSUNDEEP
sqloracle11g
BHARAT ATHOTA
BHARAT ATHOTA

Reputation: 201

Sql: Using special characters as alias for table columns?

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;

Upvotes: 6

Views: 15037

Answers (4)

user4501715
user4501715

Reputation:

Special Characters That Can Be Used in the Alias Name Field

Character
!
#
$
%
&
'
*
+
_
/
=
?
^
_
.
{
|
}
~

In oracele , double quotes can be used. I tried and working well in Toad. In SQL , heading keyword can be used as mentioned by others in answers as well.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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>

Upvotes: 4

sTg
sTg

Reputation: 4424

You can make use of doublequotes for defining special characters as alias. Tried and tested:

SELECT CustomerName AS Customer, ContactName AS "*" FROM Customers;

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

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.

Upvotes: 6

Related Questions