If you have a text field that you want to convert to a number that has a non numeric value in it, then the conversion will fail with an invalid number error. What you need is a function that determines if the string is a number, so that you can decide whether to convert or not. Sadly Oracle SQL doesn't have one.
All is not lost however, with a cunning use of translate you can suss out if a string is a number or not.
Let's say you wanted to convert JOBCODE in JOB into a number when it is numeric, and zero when it is not. The following SQL will do this for you quite nicely.
All is not lost however, with a cunning use of translate you can suss out if a string is a number or not.
Let's say you wanted to convert JOBCODE in JOB into a number when it is numeric, and zero when it is not. The following SQL will do this for you quite nicely.
select distinct JOBCODE,
( case
when translate(JOBCODE, '_0123456789', '_') is null then to_number(JOBCODE)
else 0
end )
from PS_JOB
3 comments:
I am sure this piece of writing has touched all the internet people, its really
really pleasant piece of writing on building up new blog.
my web blog ... stock market trading hours
Useful information. Fortunate me I discovered your web site
by chance, and I'm shocked why this twist of fate did not happened in advance! I bookmarked it.
Also visit my blog ... walking calculator
There are so many more ways out there to increase your natural fertility and ability to conceive, and sooner or later you'll come across the ones that work best for you. It is not possible to know for certain what your level of fertility is, indeed your fertility will change over time.
http://pregnancyhelper.in/fertility-statistics.html
Post a Comment