I have doubts regarding double quote column name in Oracle. I tried creating column name in number_alphabets pattern but this won't work. Then I used double quote and I was able to create table with this column name. When I do select, column name comes within double quote. I have attached script in here.
CREATE TABLE test
(
"100_title" VARCHAR2(200) NULL
)
SELECT * FROM test
When I do select, in result set, column name will be "100_title" but I do not want "" in it. Is there a way to fix this?
From the Database Object Names and Qualifiers documentation:
- Nonquoted identifiers cannot be Oracle Database reserved words. Quoted identifiers can be reserved words, although this is not recommended.
and
Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
Nonquoted identifiers can only contain alphanumeric characters from your database character set and the underscore (_). Database links can contain periods (.) and "at" signs (@).
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).
So your question:
When I do select, in result set, column name will be "100_title" but I do not want "" in it. Is there a way to fix this?
The column identifier 100_title starts with a non-alphabetic character so by point 6 of that documentation you must use double quotes with the identifier.
How the column name displays depends on the user interface you are using. On db<>fiddle, the column name is displayed without quotes and this will be the same with many other interfaces.
If the user interface you are using only outputs the identifier with surrounding quotes then you could change the identifier from "100_title" to title_100 as this starts with an alphabetic character and contains only alpha-numeric and underscore characters and, thus, does not need to be quoted.