Oracle - Select statement throwing an error
up vote
-3
down vote
favorite
1
Please check out this link.
You can see the error message:
Note : I intentionally kept roll_no as a character type.
I need help with the select statement.
Thanks
mysql sql oracle select
edited Nov 19 at 23:36
Alexander I.
7572620
asked Nov 19 at 21:02
HVP
82
|
show 2 more comments
up vote
-3
down vote
favorite
1
Please check out this link.
You can see the error message:
Note : I intentionally kept roll_no as a character type.
I need help with the select statement.
Thanks
mysql sql oracle select
edited Nov 19 at 23:36
Alexander I.
7572620
asked Nov 19 at 21:02
HVP
82
Oracle is not mysql, please don't play tag spam. And please post code as text, because that's what it is in reality. We don't want to have to look at a separate picture. thanks
– ADyson
Nov 19 at 21:02
Anyway it seems fairly obvious that "Unknown" and "" are not going to be convertible to numbers, so I don't why you would expect TO_NUMBER to work with them. What did you imagine the result would be? What number should "Unknown" be converted to, in your view?
– ADyson
Nov 19 at 21:04
I have a db which is screwed up. I don't have write access. And I have similar situation where I have a column which should have been in int, but ended up in archer
– HVP
Nov 19 at 21:05
And? What's your point? You still can't convert arbitrary text into a number. What are you actually trying to achieve?
– ADyson
Nov 19 at 21:06
is there any way I can do a cast to that column and get just the numbers in it. I need only numbers present in that column.
– HVP
Nov 19 at 21:08
|
show 2 more comments
up vote
-3
down vote
favorite
1
up vote
-3
down vote
favorite
1
1
Please check out this link.
You can see the error message:
Note : I intentionally kept roll_no as a character type.
I need help with the select statement.
Thanks
mysql sql oracle select
edited Nov 19 at 23:36
Alexander I.
7572620
asked Nov 19 at 21:02
HVP
82
Please check out this link.
You can see the error message:
Note : I intentionally kept roll_no as a character type.
I need help with the select statement.
Thanks
mysql sql oracle select
mysql sql oracle select
edited Nov 19 at 23:36
Alexander I.
7572620
asked Nov 19 at 21:02
HVP
82
edited Nov 19 at 23:36
Alexander I.
7572620
asked Nov 19 at 21:02
HVP
82
edited Nov 19 at 23:36
Alexander I.
7572620
edited Nov 19 at 23:36
Alexander I.
7572620
edited Nov 19 at 23:36
Alexander I.
7572620
7572620
asked Nov 19 at 21:02
HVP
82
asked Nov 19 at 21:02
HVP
82
asked Nov 19 at 21:02
HVP
82
82
Oracle is not mysql, please don't play tag spam. And please post code as text, because that's what it is in reality. We don't want to have to look at a separate picture. thanks
– ADyson
Nov 19 at 21:02
Anyway it seems fairly obvious that "Unknown" and "" are not going to be convertible to numbers, so I don't why you would expect TO_NUMBER to work with them. What did you imagine the result would be? What number should "Unknown" be converted to, in your view?
– ADyson
Nov 19 at 21:04
I have a db which is screwed up. I don't have write access. And I have similar situation where I have a column which should have been in int, but ended up in archer
– HVP
Nov 19 at 21:05
And? What's your point? You still can't convert arbitrary text into a number. What are you actually trying to achieve?
– ADyson
Nov 19 at 21:06
is there any way I can do a cast to that column and get just the numbers in it. I need only numbers present in that column.
– HVP
Nov 19 at 21:08
|
show 2 more comments
Oracle is not mysql, please don't play tag spam. And please post code as text, because that's what it is in reality. We don't want to have to look at a separate picture. thanks
– ADyson
Nov 19 at 21:02
Anyway it seems fairly obvious that "Unknown" and "" are not going to be convertible to numbers, so I don't why you would expect TO_NUMBER to work with them. What did you imagine the result would be? What number should "Unknown" be converted to, in your view?
– ADyson
Nov 19 at 21:04
I have a db which is screwed up. I don't have write access. And I have similar situation where I have a column which should have been in int, but ended up in archer
– HVP
Nov 19 at 21:05
And? What's your point? You still can't convert arbitrary text into a number. What are you actually trying to achieve?
– ADyson
Nov 19 at 21:06
is there any way I can do a cast to that column and get just the numbers in it. I need only numbers present in that column.
– HVP
Nov 19 at 21:08
Oracle is not mysql, please don't play tag spam. And please post code as text, because that's what it is in reality. We don't want to have to look at a separate picture. thanks
– ADyson
Nov 19 at 21:02
Oracle is not mysql, please don't play tag spam. And please post code as text, because that's what it is in reality. We don't want to have to look at a separate picture. thanks
– ADyson
Nov 19 at 21:02
Anyway it seems fairly obvious that "Unknown" and "" are not going to be convertible to numbers, so I don't why you would expect TO_NUMBER to work with them. What did you imagine the result would be? What number should "Unknown" be converted to, in your view?
– ADyson
Nov 19 at 21:04
Anyway it seems fairly obvious that "Unknown" and "" are not going to be convertible to numbers, so I don't why you would expect TO_NUMBER to work with them. What did you imagine the result would be? What number should "Unknown" be converted to, in your view?
– ADyson
Nov 19 at 21:04
I have a db which is screwed up. I don't have write access. And I have similar situation where I have a column which should have been in int, but ended up in archer
– HVP
Nov 19 at 21:05
I have a db which is screwed up. I don't have write access. And I have similar situation where I have a column which should have been in int, but ended up in archer
– HVP
Nov 19 at 21:05
And? What's your point? You still can't convert arbitrary text into a number. What are you actually trying to achieve?
– ADyson
Nov 19 at 21:06
And? What's your point? You still can't convert arbitrary text into a number. What are you actually trying to achieve?
– ADyson
Nov 19 at 21:06
is there any way I can do a cast to that column and get just the numbers in it. I need only numbers present in that column.
– HVP
Nov 19 at 21:08
is there any way I can do a cast to that column and get just the numbers in it. I need only numbers present in that column.
– HVP
Nov 19 at 21:08
|
show 2 more comments
3 Answers
3
active
oldest
votes
up vote
1
down vote
accepted
Not all your rows will convert to numbers. Unknown and empty string are causing the error.
answered Nov 19 at 21:05
T McKeown
11.4k11727
I need to extract values which are numbers from the column roll_no . is there any way ? And I don't have write access to this db
– HVP
Nov 19 at 21:17
but you have rows where Roll_No is not numeric.. how are you wanting to handle those? If you want to ignore them then put a WHERE clause in your SELECT, e.g. WHERE Is_Numeric( Roll_No) = 1
– T McKeown
Nov 19 at 21:20
I tried this. it says ORA-00904: "IS_NUMERIC": invalid identifier.
– HVP
Nov 19 at 21:28
update your question to show your SQL
– T McKeown
Nov 19 at 21:32
1
I think you also need to exclude nulls explicitly, WHERE Roll_No IS NOT NULL AND Is_Numeric(Roll_No) = 1, I am a SQL Server guy not sure IS NOT NULL is valid P-SQL
– T McKeown
Nov 19 at 21:50
|
show 1 more comment
up vote
0
down vote
Not everything converts as in your case a null string if you want as some value. place it in the string in the same format like To_Number('0')
answered Nov 19 at 21:20
Himanshu Ahuja
374114
add a comment |
up vote
0
down vote
This worked for me
select to_number(roll_no) from student
WHERE REGEXP_LIKE(roll_no, '^[[:digit:]]+$');
Thank you all
edited Nov 19 at 22:05
ADyson
22.2k112443
answered Nov 19 at 21:48
HVP
82
1
ugh, I personally hate reg exp but glad you were able to move on.
– T McKeown
Nov 19 at 21:49
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
Not all your rows will convert to numbers. Unknown and empty string are causing the error.
answered Nov 19 at 21:05
T McKeown
11.4k11727
I need to extract values which are numbers from the column roll_no . is there any way ? And I don't have write access to this db
– HVP
Nov 19 at 21:17
but you have rows where Roll_No is not numeric.. how are you wanting to handle those? If you want to ignore them then put a WHERE clause in your SELECT, e.g. WHERE Is_Numeric( Roll_No) = 1
– T McKeown
Nov 19 at 21:20
I tried this. it says ORA-00904: "IS_NUMERIC": invalid identifier.
– HVP
Nov 19 at 21:28
update your question to show your SQL
– T McKeown
Nov 19 at 21:32
1
I think you also need to exclude nulls explicitly, WHERE Roll_No IS NOT NULL AND Is_Numeric(Roll_No) = 1, I am a SQL Server guy not sure IS NOT NULL is valid P-SQL
– T McKeown
Nov 19 at 21:50
|
show 1 more comment
up vote
1
down vote
accepted
Not all your rows will convert to numbers. Unknown and empty string are causing the error.
answered Nov 19 at 21:05
T McKeown
11.4k11727
I need to extract values which are numbers from the column roll_no . is there any way ? And I don't have write access to this db
– HVP
Nov 19 at 21:17
but you have rows where Roll_No is not numeric.. how are you wanting to handle those? If you want to ignore them then put a WHERE clause in your SELECT, e.g. WHERE Is_Numeric( Roll_No) = 1
– T McKeown
Nov 19 at 21:20
I tried this. it says ORA-00904: "IS_NUMERIC": invalid identifier.
– HVP
Nov 19 at 21:28
update your question to show your SQL
– T McKeown
Nov 19 at 21:32
1
I think you also need to exclude nulls explicitly, WHERE Roll_No IS NOT NULL AND Is_Numeric(Roll_No) = 1, I am a SQL Server guy not sure IS NOT NULL is valid P-SQL
– T McKeown
Nov 19 at 21:50
|
show 1 more comment
up vote
1
down vote
accepted
up vote
1
down vote
accepted
Not all your rows will convert to numbers. Unknown and empty string are causing the error.
answered Nov 19 at 21:05
T McKeown
11.4k11727
Not all your rows will convert to numbers. Unknown and empty string are causing the error.
answered Nov 19 at 21:05
T McKeown
11.4k11727
edited Nov 19 at 21:10
answered Nov 19 at 21:05
T McKeown
11.4k11727
answered Nov 19 at 21:05
T McKeown
11.4k11727
answered Nov 19 at 21:05
T McKeown
11.4k11727
11.4k11727
I need to extract values which are numbers from the column roll_no . is there any way ? And I don't have write access to this db
– HVP
Nov 19 at 21:17
but you have rows where Roll_No is not numeric.. how are you wanting to handle those? If you want to ignore them then put a WHERE clause in your SELECT, e.g. WHERE Is_Numeric( Roll_No) = 1
– T McKeown
Nov 19 at 21:20
I tried this. it says ORA-00904: "IS_NUMERIC": invalid identifier.
– HVP
Nov 19 at 21:28
update your question to show your SQL
– T McKeown
Nov 19 at 21:32
1
I think you also need to exclude nulls explicitly, WHERE Roll_No IS NOT NULL AND Is_Numeric(Roll_No) = 1, I am a SQL Server guy not sure IS NOT NULL is valid P-SQL
– T McKeown
Nov 19 at 21:50
|
show 1 more comment
I need to extract values which are numbers from the column roll_no . is there any way ? And I don't have write access to this db
– HVP
Nov 19 at 21:17
but you have rows where Roll_No is not numeric.. how are you wanting to handle those? If you want to ignore them then put a WHERE clause in your SELECT, e.g. WHERE Is_Numeric( Roll_No) = 1
– T McKeown
Nov 19 at 21:20
I tried this. it says ORA-00904: "IS_NUMERIC": invalid identifier.
– HVP
Nov 19 at 21:28
update your question to show your SQL
– T McKeown
Nov 19 at 21:32
1
I think you also need to exclude nulls explicitly, WHERE Roll_No IS NOT NULL AND Is_Numeric(Roll_No) = 1, I am a SQL Server guy not sure IS NOT NULL is valid P-SQL
– T McKeown
Nov 19 at 21:50
I need to extract values which are numbers from the column roll_no . is there any way ? And I don't have write access to this db
– HVP
Nov 19 at 21:17
I need to extract values which are numbers from the column roll_no . is there any way ? And I don't have write access to this db
– HVP
Nov 19 at 21:17
but you have rows where Roll_No is not numeric.. how are you wanting to handle those? If you want to ignore them then put a WHERE clause in your SELECT, e.g. WHERE Is_Numeric( Roll_No) = 1
– T McKeown
Nov 19 at 21:20
but you have rows where Roll_No is not numeric.. how are you wanting to handle those? If you want to ignore them then put a WHERE clause in your SELECT, e.g. WHERE Is_Numeric( Roll_No) = 1
– T McKeown
Nov 19 at 21:20
I tried this. it says ORA-00904: "IS_NUMERIC": invalid identifier.
– HVP
Nov 19 at 21:28
I tried this. it says ORA-00904: "IS_NUMERIC": invalid identifier.
– HVP
Nov 19 at 21:28
update your question to show your SQL
– T McKeown
Nov 19 at 21:32
update your question to show your SQL
– T McKeown
Nov 19 at 21:32
1
1
I think you also need to exclude nulls explicitly, WHERE Roll_No IS NOT NULL AND Is_Numeric(Roll_No) = 1, I am a SQL Server guy not sure IS NOT NULL is valid P-SQL
– T McKeown
Nov 19 at 21:50
I think you also need to exclude nulls explicitly, WHERE Roll_No IS NOT NULL AND Is_Numeric(Roll_No) = 1, I am a SQL Server guy not sure IS NOT NULL is valid P-SQL
– T McKeown
Nov 19 at 21:50
|
show 1 more comment
up vote
0
down vote
Not everything converts as in your case a null string if you want as some value. place it in the string in the same format like To_Number('0')
answered Nov 19 at 21:20
Himanshu Ahuja
374114
add a comment |
up vote
0
down vote
Not everything converts as in your case a null string if you want as some value. place it in the string in the same format like To_Number('0')
answered Nov 19 at 21:20
Himanshu Ahuja
374114
add a comment |
up vote
0
down vote
up vote
0
down vote
Not everything converts as in your case a null string if you want as some value. place it in the string in the same format like To_Number('0')
answered Nov 19 at 21:20
Himanshu Ahuja
374114
Not everything converts as in your case a null string if you want as some value. place it in the string in the same format like To_Number('0')
answered Nov 19 at 21:20
Himanshu Ahuja
374114
answered Nov 19 at 21:20
Himanshu Ahuja
374114
answered Nov 19 at 21:20
Himanshu Ahuja
374114
answered Nov 19 at 21:20
Himanshu Ahuja
374114
374114
add a comment |
add a comment |
up vote
0
down vote
This worked for me
select to_number(roll_no) from student
WHERE REGEXP_LIKE(roll_no, '^[[:digit:]]+$');
Thank you all
edited Nov 19 at 22:05
ADyson
22.2k112443
answered Nov 19 at 21:48
HVP
82
1
ugh, I personally hate reg exp but glad you were able to move on.
– T McKeown
Nov 19 at 21:49
add a comment |
up vote
0
down vote
This worked for me
select to_number(roll_no) from student
WHERE REGEXP_LIKE(roll_no, '^[[:digit:]]+$');
Thank you all
edited Nov 19 at 22:05
ADyson
22.2k112443
answered Nov 19 at 21:48
HVP
82
1
ugh, I personally hate reg exp but glad you were able to move on.
– T McKeown
Nov 19 at 21:49
add a comment |
up vote
0
down vote
up vote
0
down vote
This worked for me
select to_number(roll_no) from student
WHERE REGEXP_LIKE(roll_no, '^[[:digit:]]+$');
Thank you all
edited Nov 19 at 22:05
ADyson
22.2k112443
answered Nov 19 at 21:48
HVP
82
This worked for me
select to_number(roll_no) from student
WHERE REGEXP_LIKE(roll_no, '^[[:digit:]]+$');
Thank you all
edited Nov 19 at 22:05
ADyson
22.2k112443
answered Nov 19 at 21:48
HVP
82
edited Nov 19 at 22:05
ADyson
22.2k112443
edited Nov 19 at 22:05
ADyson
22.2k112443
edited Nov 19 at 22:05
ADyson
22.2k112443
22.2k112443
answered Nov 19 at 21:48
HVP
82
answered Nov 19 at 21:48
HVP
82
answered Nov 19 at 21:48
HVP
82
82
1
ugh, I personally hate reg exp but glad you were able to move on.
– T McKeown
Nov 19 at 21:49
add a comment |
1
ugh, I personally hate reg exp but glad you were able to move on.
– T McKeown
Nov 19 at 21:49
1
1
ugh, I personally hate reg exp but glad you were able to move on.
– T McKeown
Nov 19 at 21:49
ugh, I personally hate reg exp but glad you were able to move on.
– T McKeown
Nov 19 at 21:49
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53382564%2foracle-select-statement-throwing-an-error%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
Oracle is not mysql, please don't play tag spam. And please post code as text, because that's what it is in reality. We don't want to have to look at a separate picture. thanks
– ADyson
Nov 19 at 21:02
Anyway it seems fairly obvious that "Unknown" and "" are not going to be convertible to numbers, so I don't why you would expect TO_NUMBER to work with them. What did you imagine the result would be? What number should "Unknown" be converted to, in your view?
– ADyson
Nov 19 at 21:04
I have a db which is screwed up. I don't have write access. And I have similar situation where I have a column which should have been in int, but ended up in archer
– HVP
Nov 19 at 21:05
And? What's your point? You still can't convert arbitrary text into a number. What are you actually trying to achieve?
– ADyson
Nov 19 at 21:06
is there any way I can do a cast to that column and get just the numbers in it. I need only numbers present in that column.
– HVP
Nov 19 at 21:08