1

In oracle sql,

TO_NUMBER('1,000.98', '9,999.99') gives answer as 1000.98. //line 1

TO_NUMBER('1,000.98', '9999.99') also gives answer as 1000.98. //line2

Why does line2 not give an error inspite of the format string not matching the given string ?(Format string does not have a comma)

A_J
  • 977
  • 4
  • 16
  • 44

2 Answers2

2

Oracle documentation is banned for my country, so i do not have access to it.
But in unofficial tutorials it is clear that if you use TO_NUMBER function without the third parameter(NLS_Params) it will ignore all non-digit characters and shows you the number and only recognize dot(.) as the notation for decimal point.
If you want it to recognize all other possible formatting characters used in numbers (like , for thousand separator ), you have to use the third parameter too.
Correct form will be: 

TO_NUMBER('1,000.98', '9999.99', 'nls_numeric_characters=''.,''')

Similar question with good answers is here:

StackOverFlow

Hope it helps.

Community
  • 1
  • 1
Mohamad Eghlima
  • 970
  • 10
  • 23
  • 1
    But if I do not mention the format string, how is it able to recognize the comma and gives error ? e.g If I write TO_NUMBER('1,000.98') gives error. – A_J Jan 10 '16 at 11:56
  • 1
    Also If I give the comma at some other place in the format string, e.g. TO_NUMBER('1,000.98','999,9.99') it gives error. Only if I don't give any comma, it works fine. – A_J Jan 10 '16 at 12:00
  • 1
    Even after writing the 3rd parameter, it is working the same way – A_J Jan 10 '16 at 12:04
  • 1
    @A_J Check this question and let me know if it helps. I think the selected answer will solve the problem. [http://stackoverflow.com/questions/24571355/nls-numeric-characters-setting-for-decimal] – Mohamad Eghlima Jan 10 '16 at 12:14
  • What I understood from the link: comma is the default NLS_NUMERIC_CHARACTER that is why it is working even without mentioning in the format string. But I am not sure if I understood correctly. It would be very helpful if you could explain it. – A_J Jan 10 '16 at 13:09
  • @A_J What you get for _your current session settings by querying nls_session_parameters_ as it said in the link? `select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';` – Mohamad Eghlima Jan 10 '16 at 13:49
  • dot and then comma, so my decimal separator is dot and group separator is comma I think – A_J Jan 10 '16 at 13:53
  • FYI, I am using Oracle 11g – A_J Jan 10 '16 at 13:55
  • @A_J I think i just got what you mean :P. for your three first comments; It is not necessary to pass the second parameter. oracle uses `NLS_NUMERIC_CHARACTERS` implicitly to format numbers. – Mohamad Eghlima Jan 10 '16 at 14:59
  • But the problem is that if I do not pass the 2nd parameter, Oracle gives error with comma in the string but if I pass the second parameter with a format not having any comma it does not give any error – A_J Jan 10 '16 at 15:05
  • @A_J look at this documentation please: http://gerardnico.com/wiki/database/oracle/to_number It says: _The format value must have this form: 'dg' where : _ I think your NLS_PARAM is right but in formatting you have to have these two sign. sorry that I can not test them myself but I look forward to hear the result from you. – Mohamad Eghlima Jan 10 '16 at 15:12
  • @A_J I think that is because with format as the second parameter, oracle assumes that you want to ignore `,` , because there is no sign of g in the param. I am curious now to know the result :D – Mohamad Eghlima Jan 10 '16 at 15:14
  • I am not getting what you want me to check ? I already told you that my DG sequence is dot and then comma – A_J Jan 10 '16 at 15:21
  • select to_number('1,00.12','9G99D99') from dual; If I write this query I get the output as 100.12 – A_J Jan 10 '16 at 15:27
  • Whether I write 'G' or not it is working the same way, i.e it is accepting the comma in the string passed why it is so ? – A_J Jan 10 '16 at 15:29
  • @A_J I am looking for a machine to test all these. if your session settings are correct It might be database connection settings or If you are using pl/sql developer, maybe that IDE has special setting in regard? I will check these and come back again. – Mohamad Eghlima Jan 11 '16 at 06:51
1

TO_NUMBER documentation is kind confusing:

TO_NUMBER converts expr to a value of NUMBER data type. The expr can be a BINARY_DOUBLE value or a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type containing a number in the format specified by the optional format model fmt.

Despite this Oracle only cares for decimal separator from the source string when doing the conversion so its ignores thousands group markers, and so forth. This behavior seems not to be documented anywhere.

The separators markers are set by NLS_NUMERIC_CHARACTERS.

The NLS_NUMERIC_CHARACTERS option specifies the decimal marker and thousands group marker for the session.

For a safest conversion consider CAST:

SQL> SELECT CAST('1000,98' as DECIMAL(10,2)) as TO_NUMBER FROM DUAL;

 TO_NUMBER
----------
   1000,98
Juan Diego Godoy Robles
  • 14,447
  • 2
  • 38
  • 52
  • What is the default format ? I am asking becoz TO_NUMBER('1,0,0,0,00.98', '999999.99') is also working. In spite of giving so many commas and the format string has no commas, still it is accepting the value, Can you please explain why ? – A_J Jan 10 '16 at 11:11