Oracle - Select statement throwing an error

up vote
-3
down vote

favorite

Please check out this link.

You can see the error message:

enter image description here

Note : I intentionally kept roll_no as a character type.
I need help with the select statement.

Thanks

edited Nov 19 at 23:36

Alexander I.

7572620

asked Nov 19 at 21:02

HVP

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

Please check out this link.

You can see the error message:

enter image description here

Note : I intentionally kept roll_no as a character type.
I need help with the select statement.

Thanks

edited Nov 19 at 23:36

Alexander I.

7572620

asked Nov 19 at 21:02

HVP

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

Please check out this link.

You can see the error message:

enter image description here

Note : I intentionally kept roll_no as a character type.
I need help with the select statement.

Thanks

edited Nov 19 at 23:36

Alexander I.

7572620

asked Nov 19 at 21:02

HVP

Please check out this link.

You can see the error message:

enter image description here

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

edited Nov 19 at 23:36

Alexander I.

7572620

asked Nov 19 at 21:02

HVP

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

asked Nov 19 at 21:02

HVP

asked Nov 19 at 21:02

HVP

asked Nov 19 at 21:02

HVP

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

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

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.

edited Nov 19 at 21:10

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

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 |

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',
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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

3 Answers
3

active

oldest

votes

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.

edited Nov 19 at 21:10

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.

edited Nov 19 at 21:10

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.

edited Nov 19 at 21:10

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.

edited Nov 19 at 21:10

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

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

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

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

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

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

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

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

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

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

answered Nov 19 at 21:48

HVP

answered Nov 19 at 21:48

HVP

answered Nov 19 at 21:48

HVP

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

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ytukyg