ORACLE/SQL: Wildcard literally in a column name
I have a table in which a column name begins with a wildcard. So one of the column is named _Test_
How can I access this column?
select _TEST_ from Table
throws an error, as well as
select *
from Table
where _TEST_ = 123
I tried suggestions with "escape", using [_TEST_], or _TEST_ but nothing worked. I cannot change the table.
EDIT (Thanks to @Alex Poole) : select * from Table Where "_TEST_"=123 works. But select "_TEST_" from Table does not.
sql oracle
edited Nov 22 at 20:46
marc_s
570k12811021250
asked Nov 22 at 18:36
kiara
1585
add a comment |
I have a table in which a column name begins with a wildcard. So one of the column is named _Test_
How can I access this column?
select _TEST_ from Table
throws an error, as well as
select *
from Table
where _TEST_ = 123
I tried suggestions with "escape", using [_TEST_], or _TEST_ but nothing worked. I cannot change the table.
EDIT (Thanks to @Alex Poole) : select * from Table Where "_TEST_"=123 works. But select "_TEST_" from Table does not.
sql oracle
edited Nov 22 at 20:46
marc_s
570k12811021250
asked Nov 22 at 18:36
kiara
1585
2
DoesSELECT * FROM "_TEST_"work?
– Salman A
Nov 22 at 18:38
4
See the answer here on double-quotes in queries to oracle dbs: stackoverflow.com/a/13798120/4636715
– vahdet
Nov 22 at 18:39
@SalmanASelect "_TEST_" from Tabledoes not work.
– kiara
Nov 22 at 18:43
2
It isn't a wildcard in that context, so escaping isn't relevant (both apply tolike); but the rules mean it must be a quoted identifier. You have to match the case exactly too - soselect * from Table Where "_Test_"=123, based on what you said the column is actually called. (This is why you should avoid using quoted identifiers...)
– Alex Poole
Nov 22 at 18:44
select "_Test_" from table
– Himanshu Ahuja
Nov 22 at 19:00
add a comment |
I have a table in which a column name begins with a wildcard. So one of the column is named _Test_
How can I access this column?
select _TEST_ from Table
throws an error, as well as
select *
from Table
where _TEST_ = 123
I tried suggestions with "escape", using [_TEST_], or _TEST_ but nothing worked. I cannot change the table.
EDIT (Thanks to @Alex Poole) : select * from Table Where "_TEST_"=123 works. But select "_TEST_" from Table does not.
sql oracle
edited Nov 22 at 20:46
marc_s
570k12811021250
asked Nov 22 at 18:36
kiara
1585
I have a table in which a column name begins with a wildcard. So one of the column is named _Test_
How can I access this column?
select _TEST_ from Table
throws an error, as well as
select *
from Table
where _TEST_ = 123
I tried suggestions with "escape", using [_TEST_], or _TEST_ but nothing worked. I cannot change the table.
EDIT (Thanks to @Alex Poole) : select * from Table Where "_TEST_"=123 works. But select "_TEST_" from Table does not.
sql oracle
sql oracle
edited Nov 22 at 20:46
marc_s
570k12811021250
asked Nov 22 at 18:36
kiara
1585
edited Nov 22 at 20:46
marc_s
570k12811021250
asked Nov 22 at 18:36
kiara
1585
edited Nov 22 at 20:46
marc_s
570k12811021250
edited Nov 22 at 20:46
marc_s
570k12811021250
edited Nov 22 at 20:46
marc_s
570k12811021250
570k12811021250
asked Nov 22 at 18:36
kiara
1585
asked Nov 22 at 18:36
kiara
1585
asked Nov 22 at 18:36
kiara
1585
1585
2
DoesSELECT * FROM "_TEST_"work?
– Salman A
Nov 22 at 18:38
4
See the answer here on double-quotes in queries to oracle dbs: stackoverflow.com/a/13798120/4636715
– vahdet
Nov 22 at 18:39
@SalmanASelect "_TEST_" from Tabledoes not work.
– kiara
Nov 22 at 18:43
2
It isn't a wildcard in that context, so escaping isn't relevant (both apply tolike); but the rules mean it must be a quoted identifier. You have to match the case exactly too - soselect * from Table Where "_Test_"=123, based on what you said the column is actually called. (This is why you should avoid using quoted identifiers...)
– Alex Poole
Nov 22 at 18:44
select "_Test_" from table
– Himanshu Ahuja
Nov 22 at 19:00
add a comment |
2
DoesSELECT * FROM "_TEST_"work?
– Salman A
Nov 22 at 18:38
4
See the answer here on double-quotes in queries to oracle dbs: stackoverflow.com/a/13798120/4636715
– vahdet
Nov 22 at 18:39
@SalmanASelect "_TEST_" from Tabledoes not work.
– kiara
Nov 22 at 18:43
2
It isn't a wildcard in that context, so escaping isn't relevant (both apply tolike); but the rules mean it must be a quoted identifier. You have to match the case exactly too - soselect * from Table Where "_Test_"=123, based on what you said the column is actually called. (This is why you should avoid using quoted identifiers...)
– Alex Poole
Nov 22 at 18:44
select "_Test_" from table
– Himanshu Ahuja
Nov 22 at 19:00
2
2
Does
SELECT * FROM "_TEST_" work?– Salman A
Nov 22 at 18:38
Does
SELECT * FROM "_TEST_" work?– Salman A
Nov 22 at 18:38
4
4
See the answer here on double-quotes in queries to oracle dbs: stackoverflow.com/a/13798120/4636715
– vahdet
Nov 22 at 18:39
See the answer here on double-quotes in queries to oracle dbs: stackoverflow.com/a/13798120/4636715
– vahdet
Nov 22 at 18:39
@SalmanA
Select "_TEST_" from Table does not work.– kiara
Nov 22 at 18:43
@SalmanA
Select "_TEST_" from Table does not work.– kiara
Nov 22 at 18:43
2
2
It isn't a wildcard in that context, so escaping isn't relevant (both apply to
like); but the rules mean it must be a quoted identifier. You have to match the case exactly too - so select * from Table Where "_Test_"=123, based on what you said the column is actually called. (This is why you should avoid using quoted identifiers...)– Alex Poole
Nov 22 at 18:44
It isn't a wildcard in that context, so escaping isn't relevant (both apply to
like); but the rules mean it must be a quoted identifier. You have to match the case exactly too - so select * from Table Where "_Test_"=123, based on what you said the column is actually called. (This is why you should avoid using quoted identifiers...)– Alex Poole
Nov 22 at 18:44
select "_Test_" from table– Himanshu Ahuja
Nov 22 at 19:00
select "_Test_" from table– Himanshu Ahuja
Nov 22 at 19:00
add a comment |
1 Answer
1
active
oldest
votes
An underscore is only a wildcard for the like pattern-matching condition. In the context you are trying to use it, it is not a wildcard. The column name just contains an underscore.
The documenation on Database Object Names and Qualifiers shows the rules for object names, including rule 6:
Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
As your column name starts with an underscore, the column must have been defined with a quoted identifier when the table was created, e.g.:
create table your_table (test number, "_Test_" number);
You can see the exact name for each column in the data dictionary:
COLUMN_NAME
------------------------------
TEST
_Test_
If a column (or any object) follows the non-quoted-identifier rules then it is recorded in uppercase in the data dictionary, but you can refer to it without quotes and with any case; so any of these are valid:
select * from your_table where TEST = 123;
select * from your_table where Test = 123;
select * from your_table where test = 123;
select * from your_table where tEsT = 123;
But if for a quoted identifier you always have to uses quotes and exactly the same case when referring to it. So these error:
select * from your_table where _TEST_ = 123;
select * from your_table where _Test_ = 123;
select * from your_table where "_TEST_" = 123;
(the first two with ORA-00911: invalid character, the third with ORA-00904: "_TEST_": invalid identifier because of the case difference). You have to match exactly, so only this is valid:
select * from your_table where "_Test_" = 123;
This is why quoted identifiers are a pain to work with and should be avoided if possible. Oracle even advise against using them.
answered Nov 22 at 19:00
Alex Poole
129k6101176
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436562%2foracle-sql-wildcard-literally-in-a-column-name%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
An underscore is only a wildcard for the like pattern-matching condition. In the context you are trying to use it, it is not a wildcard. The column name just contains an underscore.
The documenation on Database Object Names and Qualifiers shows the rules for object names, including rule 6:
Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
As your column name starts with an underscore, the column must have been defined with a quoted identifier when the table was created, e.g.:
create table your_table (test number, "_Test_" number);
You can see the exact name for each column in the data dictionary:
COLUMN_NAME
------------------------------
TEST
_Test_
If a column (or any object) follows the non-quoted-identifier rules then it is recorded in uppercase in the data dictionary, but you can refer to it without quotes and with any case; so any of these are valid:
select * from your_table where TEST = 123;
select * from your_table where Test = 123;
select * from your_table where test = 123;
select * from your_table where tEsT = 123;
But if for a quoted identifier you always have to uses quotes and exactly the same case when referring to it. So these error:
select * from your_table where _TEST_ = 123;
select * from your_table where _Test_ = 123;
select * from your_table where "_TEST_" = 123;
(the first two with ORA-00911: invalid character, the third with ORA-00904: "_TEST_": invalid identifier because of the case difference). You have to match exactly, so only this is valid:
select * from your_table where "_Test_" = 123;
This is why quoted identifiers are a pain to work with and should be avoided if possible. Oracle even advise against using them.
answered Nov 22 at 19:00
Alex Poole
129k6101176
add a comment |
An underscore is only a wildcard for the like pattern-matching condition. In the context you are trying to use it, it is not a wildcard. The column name just contains an underscore.
The documenation on Database Object Names and Qualifiers shows the rules for object names, including rule 6:
Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
As your column name starts with an underscore, the column must have been defined with a quoted identifier when the table was created, e.g.:
create table your_table (test number, "_Test_" number);
You can see the exact name for each column in the data dictionary:
COLUMN_NAME
------------------------------
TEST
_Test_
If a column (or any object) follows the non-quoted-identifier rules then it is recorded in uppercase in the data dictionary, but you can refer to it without quotes and with any case; so any of these are valid:
select * from your_table where TEST = 123;
select * from your_table where Test = 123;
select * from your_table where test = 123;
select * from your_table where tEsT = 123;
But if for a quoted identifier you always have to uses quotes and exactly the same case when referring to it. So these error:
select * from your_table where _TEST_ = 123;
select * from your_table where _Test_ = 123;
select * from your_table where "_TEST_" = 123;
(the first two with ORA-00911: invalid character, the third with ORA-00904: "_TEST_": invalid identifier because of the case difference). You have to match exactly, so only this is valid:
select * from your_table where "_Test_" = 123;
This is why quoted identifiers are a pain to work with and should be avoided if possible. Oracle even advise against using them.
answered Nov 22 at 19:00
Alex Poole
129k6101176
add a comment |
An underscore is only a wildcard for the like pattern-matching condition. In the context you are trying to use it, it is not a wildcard. The column name just contains an underscore.
The documenation on Database Object Names and Qualifiers shows the rules for object names, including rule 6:
Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
As your column name starts with an underscore, the column must have been defined with a quoted identifier when the table was created, e.g.:
create table your_table (test number, "_Test_" number);
You can see the exact name for each column in the data dictionary:
COLUMN_NAME
------------------------------
TEST
_Test_
If a column (or any object) follows the non-quoted-identifier rules then it is recorded in uppercase in the data dictionary, but you can refer to it without quotes and with any case; so any of these are valid:
select * from your_table where TEST = 123;
select * from your_table where Test = 123;
select * from your_table where test = 123;
select * from your_table where tEsT = 123;
But if for a quoted identifier you always have to uses quotes and exactly the same case when referring to it. So these error:
select * from your_table where _TEST_ = 123;
select * from your_table where _Test_ = 123;
select * from your_table where "_TEST_" = 123;
(the first two with ORA-00911: invalid character, the third with ORA-00904: "_TEST_": invalid identifier because of the case difference). You have to match exactly, so only this is valid:
select * from your_table where "_Test_" = 123;
This is why quoted identifiers are a pain to work with and should be avoided if possible. Oracle even advise against using them.
answered Nov 22 at 19:00
Alex Poole
129k6101176
An underscore is only a wildcard for the like pattern-matching condition. In the context you are trying to use it, it is not a wildcard. The column name just contains an underscore.
The documenation on Database Object Names and Qualifiers shows the rules for object names, including rule 6:
Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
As your column name starts with an underscore, the column must have been defined with a quoted identifier when the table was created, e.g.:
create table your_table (test number, "_Test_" number);
You can see the exact name for each column in the data dictionary:
COLUMN_NAME
------------------------------
TEST
_Test_
If a column (or any object) follows the non-quoted-identifier rules then it is recorded in uppercase in the data dictionary, but you can refer to it without quotes and with any case; so any of these are valid:
select * from your_table where TEST = 123;
select * from your_table where Test = 123;
select * from your_table where test = 123;
select * from your_table where tEsT = 123;
But if for a quoted identifier you always have to uses quotes and exactly the same case when referring to it. So these error:
select * from your_table where _TEST_ = 123;
select * from your_table where _Test_ = 123;
select * from your_table where "_TEST_" = 123;
(the first two with ORA-00911: invalid character, the third with ORA-00904: "_TEST_": invalid identifier because of the case difference). You have to match exactly, so only this is valid:
select * from your_table where "_Test_" = 123;
This is why quoted identifiers are a pain to work with and should be avoided if possible. Oracle even advise against using them.
answered Nov 22 at 19:00
Alex Poole
129k6101176
answered Nov 22 at 19:00
Alex Poole
129k6101176
answered Nov 22 at 19:00
Alex Poole
129k6101176
answered Nov 22 at 19:00
Alex Poole
129k6101176
129k6101176
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436562%2foracle-sql-wildcard-literally-in-a-column-name%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Does
SELECT * FROM "_TEST_"work?– Salman A
Nov 22 at 18:38
4
See the answer here on double-quotes in queries to oracle dbs: stackoverflow.com/a/13798120/4636715
– vahdet
Nov 22 at 18:39
@SalmanA
Select "_TEST_" from Tabledoes not work.– kiara
Nov 22 at 18:43
2
It isn't a wildcard in that context, so escaping isn't relevant (both apply to
like); but the rules mean it must be a quoted identifier. You have to match the case exactly too - soselect * from Table Where "_Test_"=123, based on what you said the column is actually called. (This is why you should avoid using quoted identifiers...)– Alex Poole
Nov 22 at 18:44
select "_Test_" from table– Himanshu Ahuja
Nov 22 at 19:00