OTN Discussion Forums : to_number default format ...

Laurent Schneider

Posts: 5,379
Registered: 02/25/01

to_number default format
Posted: May 27, 2004 1:08 AM

My question is actually related to my answer to http://forums.oracle.com/forums/thread.jsp?forum=75&thread=246241&tstart=0&trange=100

What is the default format for to_number? The default format for to_char(number) is TM9, but I cannot figure out the default format for to_number.

Ok, here is my puzzle :

SQL> alter session set nls_numeric_characters=',.';
 
SQL> select STRING, to_number(string) from x;
 
STRING          TO_NUMBER(STRING)
--------------- -----------------
-0,000000000001        -1,000E-12
999999999999999        1,0000E+15
1e+25                  1,0000E+25

try to do this without alter session but with to_number(string,<fmt>,'nls_numeric_characters='',.''');

An ice-cream for the first to find out the right fmt

Regards
Laurent

2889

Posts: 999
Registered: 04/01/98

Re: to_number default format
Posted: May 27, 2004 4:10 AM

in response to: Laurent Schneider

Reply

roll your own NLS-converter :

col value format 9.9999999999999EEEE

select string, to_number(translate(string , ',.' , '.' )) value
from ( select '-0,000000000001' string from dual
union select '999999999999999' string from dual
union select '1E+25' string from dual
union select '123.456.789,012345' string from dual
) A
;

STRING VALUE

---------------------
-0,000000000001 -1.0000000000000E-12
123.456.789,012345 1.2345678901235E+08
1E+25 1.0000000000000E+25
999999999999999 1.0000000000000E+15

didn't find any other way out so far

APC

Posts: 10,438
Registered: 08/27/03

Re: to_number default format
Posted: May 27, 2004 4:36 AM

in response to: Laurent Schneider

Reply

Laurent

I don't think we can specify an explicit format mask that handles both regular numbers and scientific notation. Obviously, implicit number conversion is smart enough to do so, but that doesn't allow us to define our decimal notation on the fly.

So I guess you get to keep the ice cream. However, I prepared to be astounded.

Cheers, APC

Laurent Schneider

Posts: 5,379
Registered: 02/25/01

Re: to_number default format
Posted: May 27, 2004 5:14 AM

in response to: APC

Reply

Thanks you both for your answers

Here is my own proposition

select string,
  decode(instr(upper(string), 'E'),0,TO_NUMBER(STRING,'999999999999999999999999999999D999999999999999999999999999999','NLS_NUMERIC_CHARACTERS='',.'''),
  TO_NUMBER(STRING,'999999999999999999999999999999D999999999999999999999999999999EEEE','NLS_NUMERIC_CHARACTERS='',.''') ) "NUMBER"
from x;

Laurent Schneider

Posts: 5,379
Registered: 02/25/01

Re: to_number default format
Posted: May 27, 2004 5:20 AM

in response to: Laurent Schneider

Reply

or
TO_NUMBER(STRING,'999999999999999999999999999999D999999999999999999999999999999'||decode(instr(upper(string),'E'),0,'','EEEE') ,'NLS_NUMERIC_CHARACTERS='',.''')

but still not worth an ice-cream

nbellam

Posts: 2,795
Registered: 02/19/00

Re: to_number default format
Posted: May 27, 2004 6:47 AM

in response to: Laurent Schneider

Reply

I have any easy way for you.

SQL> select string, to_number(string) from x;

STRING TO_NUMBER(STRING)

-----------------
-0.000000000001 -1.000E-12
999999999999999 1.0000E+15
1e+25 1.0000E+25

SQL> show numwidth
numwidth 10
SQL> set numwidth 30
SQL> select string, to_number(string) from x;

STRING TO_NUMBER(STRING)

------------------------------
-0.000000000001 -.000000000001
999999999999999 999999999999999
1e+25 10000000000000000000000000

APC

Posts: 10,438
Registered: 08/27/03

Re: to_number default format
Posted: May 27, 2004 6:58 AM

in response to: nbellam

Reply

There's a eason why we didn't take the "easy" route: it misses the need for nls_numeric_characters=',.'

Take a closer look at Laurent's original posting - the first number is -0,000000000001 not -0.000000000001

The devil is in the detail. -- Blixa Bargeld

Cheers, APC

nbellam

Posts: 2,795
Registered: 02/19/00

Re: to_number default format
Posted: May 27, 2004 7:11 AM

in response to: APC

Reply

SQL> alter session set nls_numeric_characters = ',.';

Session altered.

SQL> select string, to_number(string) from x;

STRING TO_NUMBER(STRING)

-----------------
999999999999999 1,0000E+15
1e+25 1,0000E+25
-0,000000000001 -1,000E-12

SQL> show numwidth
numwidth 10
SQL> set numwidth 30
SQL> select string, to_number(string) from x;

STRING TO_NUMBER(STRING)

------------------------------
999999999999999 999999999999999
1e+25 10000000000000000000000000
-0,000000000001 -,000000000001

APC

Posts: 10,438
Registered: 08/27/03

Re: to_number default format
Posted: May 27, 2004 7:39 AM

in response to: nbellam

Reply

As Q might say, Pay attention Agent 355099.

Laurent gave that as a way of doing it in the original posting. To win the ice cream we need to do everything in a single SQL statement.

Cheers, APC

Laurent Schneider

Posts: 5,379
Registered: 02/25/01

Re: to_number default format
Posted: May 27, 2004 7:46 AM

in response to: nbellam

Reply

Hi 355099,
What a nice prime number you have !

The puzzle was to do it without an alter session, for example in a view.

Regards
Laurent

nbellam

Posts: 2,795
Registered: 02/19/00

Re: to_number default format
Posted: May 27, 2004 8:14 AM

in response to: Laurent Schneider

Reply

I did not use an alter session command here.

SQL> select * from nls_session_parameters
2 where parameter = 'NLS_NUMERIC_CHARACTERS';

PARAMETER VALUE

----------------------------------
NLS_NUMERIC_CHARACTERS .,

SQL> select string, to_number(replace(string,',','.')) from x;

STRING TO_NUMBER(REPLACE(STRING,',','.'))

----------------------------------
999999999999999 1.0000E+15
1e+25 1.0000E+25
-0,000000000001 -1.000E-12

2889

Posts: 999
Registered: 04/01/98

Re: to_number default format
Posted: May 27, 2004 8:56 AM

in response to: nbellam

Reply

355100-1 : run for the spectacles

SQL> select replace('-0,000000000001' , ',' , 'COMMA') from dual ;



-0COMMA000000000001




SQL> select replace('-0.000000000001' , '.' , 'DOT') from dual ;



-0DOT000000000001

nbellam

Posts: 2,795
Registered: 02/19/00

Re: to_number default format
Posted: May 27, 2004 9:02 AM

in response to: 2889

Reply

2889-
Thanks for your feedback.
I am familiar with the way the function 'replace' operates.

2889

Posts: 999
Registered: 04/01/98

Re: to_number default format
Posted: May 27, 2004 1:04 PM

in response to: nbellam

Reply

Dear 355099,

no harm intended, e-mail is really destroying the fine art of writing letters, sometimes reflecting the current stress at work, there is a difference between pushing RETURN and closing an envelope, putting a stamp, inserting it physically into a mail box, should have known better.

Just tried to demonstrate the easily overlooked differences, suffering myself from slowly (luckily very slowly) deteriation of sight, always looking for optimum of price and size of monitors.

Keep up your good contributions, bear with me, back to the saltmines.