TO_NUMBER [message #361640] |
Thu, 27 November 2008 04:11 |
|
MarcS
Messages: 312 Registered: March 2007 Location: Antwerp
|
Senior Member |
|
|
For one of my projects I got following problem to tackle:
Quote: |
The value is always with 8 decimals after the decimal sign and maximum 10 figures before the decimal sign.
e.g.: 0.99723458
|
And as always I'm looking for the simplest - read: as less code as possible - solution.
And I came up with this:
SQL> SELECT TO_NUMBER(:value_to_convert,'9999999999D99999999')
2> FROM DUAL
3> /
It'll raise Oracle error ORA-01722: invalid number when you pass a string like '0.123456789'.
But it'll return a value when passing '0.1234567' whereas it should give an error as well, due to only 7 figures after the decimal sign.
Anyone else who had to tackle this kind of validations and is willing to share his/her solution?
|
|
|
|
Re: TO_NUMBER [message #361648 is a reply to message #361643] |
Thu, 27 November 2008 04:32 |
|
MarcS
Messages: 312 Registered: March 2007 Location: Antwerp
|
Senior Member |
|
|
JRowbottom wrote on Thu, 27 November 2008 11:16 | Hmmm....
what are you planning to do when the actual number is 0.5 - do you want to make the user add 7 trailing 0s to the number?
Even if you did add the 0s to it, the actual number is still just 0.5
If you're creating a column, just create it as NUMBER(18,8 ) and everything will be fine.
|
I would say: welcome to the world where your clients can ask the most silliest things
It is specified like that in the request from the client, and it should be implemented like that, no questions asked.
And yes: 0.5 should be entered as 0.50000000, how crazy that may sound. Entering as in read from a external file.
And yes: I know about NUMBER(12,2) and so, but that'll only tackle overflow and not "underflow".
Now, if implementing the "underflow" part is too costly, I might convince the client to re-consider.
|
|
|
|
Re: TO_NUMBER [message #361654 is a reply to message #361648] |
Thu, 27 November 2008 04:47 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If they're using 10g, you could beat themn with a regular expression.
This should check that there are between 1 and 10 leading digits, followed by a period, followed by exactly 8 trailing digits:with src as (select '1234.50000000' col_1 from dual union all
select '1234.5000000' col_1 from dual union all
select '12345000000' col_1 from dual union all
select '12345678901.50000000' col_1 from dual)
select col_1
from src
where regexp_like(col_1,'^[0-9]{1,10}\.[0-9]{8}$');
|
|
|
Re: TO_NUMBER [message #361655 is a reply to message #361654] |
Thu, 27 November 2008 04:55 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or you could check the length of the strings before and after the period like this:with src as (select '1234.50000000' col_1 from dual union all
select '1234.5000000' col_1 from dual union all
select '12345000000' col_1 from dual union all
select '12345678901.50000000' col_1 from dual)
select length(substr(col_1,1,instr(col_1,'.')-1))
,length(substr(col_1,instr(col_1,'.')+1))
from src;
I prefer the regexp though, as it checks that all the characters are numbers, and that there is only one period.
|
|
|
Re: TO_NUMBER [message #361657 is a reply to message #361640] |
Thu, 27 November 2008 04:58 |
|
MarcS
Messages: 312 Registered: March 2007 Location: Antwerp
|
Senior Member |
|
|
Thanks for the replies!
JRowbottom, I didn't think about REGEXP as a possibility.
I'm still not used having that around in 10g.
|
|
|