Re: numeric_to_number() function skipping some digits - Mailing list pgsql-hackers

Added to TODO:
|Fix to_number() handling for values not matching the format string



---------------------------------------------------------------------------

Jeevan Chalke wrote:
> Hi,
> 
> On Mon, Sep 21, 2009 at 12:36 PM, Brendan Jurd <direvus@gmail.com> wrote:
> 
> > 2009/9/21 Jeevan Chalke <jeevan.chalke@enterprisedb.com>:
> > > Oracle returns "19-SEP-09" irrespective of the format.
> > > Here in PG, we have getting the proper date irrespective of the format as
> > > Oracle. But in the case to to_number the returned value is wrong. For
> > > example following query returns '340' on PG where as it returns '3450' on
> > > Oracle.
> > >
> > > select to_number('34,50','999,99') from dual;
> > >
> >
> > Hi Jeevan,
> >
> > Thanks for checking up on the Oracle behaviour.  It appears to
> > silently disregard grouping characters in the format pattern, and also
> > disregard them wherever they appear in the input string (or else it
> > reads the string from right-to-left?).
> >
> 
> It seems that Oracle reads formatting string from right-to-left. Here are
> few results:
> ('number','format') ==> Oracle          PG
> --------------------------------------------
> ('34,50','999,99')  ==> 3450            340
> ('34,50','99,99')   ==> 3450            3450
> ('34,50','99,999')  ==> Invalid Number  3450
> ('34,50','999,999') ==> Invalid Number  340
> 
> 
> >
> > It seems that, to match Oracle, we'd need to teach the code that 'G'
> > and ',' are no-ops for to_number(), and also that such characters
> > should be ignored in the input.
> >
> 
> That means we cannot simply ignore such characters from the input. Rather we
> can process the string R-L. But yes this will definitely going to break the
> current applications running today.
> 
> 
> > To be honest, though, I'm not sure it's worth pursuing.  If you want
> > to feed in numbers that have decorative characters all through them,
> > it's far more predictable to just regex out the cruft and use ordinary
> > numeric parsing than to use to_number(), which is infamous for its
> > idiosyncrasies:
> >
> > # SELECT regexp_replace('34,50', E'[\\d.]', '', 'g')::numeric;
> > 3450
> >
> 
> This (with E'[^\\d.]') ignores/replaces all the characters except digits
> from the input which we certainly not wishing to do. Instead we can continue
> with the current implementation. But IMHO, somewhere in the time-line we
> need to fix this.
> 
> 
> > Cheers,
> > BJ
> >
> 
> 
> Thanks
> -- 
> Jeevan B Chalke
> EnterpriseDB Software India Private Limited, Pune
> Visit us at: www.enterprisedb.com
> ---
> If better is possible, then good is not enough

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.comPG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard
drive,Christ can be your backup. +