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