How to convert string field and use for Where clause.Am getting exception like this please help to find the wrong thing.
select * from student
where (cast (nvl(linerevnum,'0') as int)) = 1
linerevnum is varchar2
Exception : invalid number
-
3this is because, some row, has a non numeric value stored in this column.dani herrera– dani herrera2013-10-29 09:26:06 +00:00Commented Oct 29, 2013 at 9:26
-
even after checking null also same thing happens.Thank you for your reply.Updated the Question.Jericho– Jericho2013-10-29 09:28:03 +00:00Commented Oct 29, 2013 at 9:28
-
he never said null, he said non numeric values... Read the compatibility here docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj33562.htmlSrini V– Srini V2013-10-29 09:29:08 +00:00Commented Oct 29, 2013 at 9:29
Add a comment
|
1 Answer
Compare only when is numeric
select * from student
where
(
case when ISNUMERIC( linerevnum )
then cast (linerevnum as int)
else null
end ) = 1
or simple:
select * from student
linerevnum = '1'
4 Comments
webLacky3rdClass
GOOGLERS!!! Change 'int' to 'SIGNED', 'UNSIGNED' or 'DECIMAL(xx)' bare int is not valid in MySQL CAST()
dani herrera
@webLacky3rdClass, MYSQL? Who talks about MYSQL? It's oracle cast function. Is GOOGLERS a kind of insult?? UNREADERS, read tags before write comments ;)
webLacky3rdClass
This was was still high in the search for MySQL CAST, so it seemed appropriate to have the same for MySQL. Tag or not it is still what comes up in a search. If Googlers is some sort of insult, well, then I think, most people reading this will chant curses at me {"May your servers UPS fail!","May a buffer overflow come upon you!","Let the NSA know your private keys!"} that sort of thing ;)
dani herrera
@webLacky3rdClass, Ooooook! Now I understand. Just advice to people who comes from google! Ok. Thanks!
