Hi, On Mon, Sep 21, 2009 at 12:36 PM, Brendan Jurd <dire...@gmail.com> wrote:
> 2009/9/21 Jeevan Chalke <jeevan.cha...@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