Home » SQL & PL/SQL » SQL & PL/SQL » TO_NUMBER (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production)
TO_NUMBER [message #361640] Thu, 27 November 2008 04:11 Go to next message
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 #361643 is a reply to message #361640] Thu, 27 November 2008 04:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
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,Cool and everything will be fine.

Re: TO_NUMBER [message #361648 is a reply to message #361643] Thu, 27 November 2008 04:32 Go to previous messageGo to next message
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 Wink

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 #361652 is a reply to message #361648] Thu, 27 November 2008 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check if the length of the string after '.' is between 8 and 10.

Regards
Michel
Re: TO_NUMBER [message #361654 is a reply to message #361648] Thu, 27 November 2008 04:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.

Previous Topic: ORA-22922
Next Topic: SQL for getting list of active accounts
Goto Forum:
  


Current Time: Tue Nov 05 12:45:35 CST 2024