How to convert from string to number in Oracle using TO_NUMBER function with fixed decimal point char?

Question

I need to convert in procedure from string to decimal with fixed decimal separator . independant on culture settings. Next, I know decimal number is limited just with 6 decimal places after . and there is no limitiation on number of digits before .. Using Oracle documentation and its examples for format strings I have now just this solution:

v_number := TO_NUMBER(v_string, '9999999999999999999999999999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''. ''');

Number of 9 chars before D is maximum number allowed. I find this format string as pretty awful. Is there any better format string for this general conversion or some way to omit second parameter of function? In general I just need to pass to function NLS parameter to tell it i just want to convert with decimal separator ., but second parameter is mandatory in that case as well.

Solution 1

You can't call the to_number function with the third parameter and not the second. I would suggest putting the "ugly" format string in a package constant and forget about it.

You could also use dbms_session.set_nls to modify your NLS settings and be able to use to_number without arguments.

Solution 2

Handles both comma and period.

FUNCTION to_number2(p_num_str VARCHAR2) RETURN NUMBER AS
BEGIN
  RETURN TO_NUMBER(REPLACE(p_num_str, ',', '.'), '999999999999D999999999999', 'NLS_NUMERIC_CHARACTERS=''.,''');
END;

Comments

[Rob van Wijk]

+1 ... or ,using the same line of thought, you can create your own (packaged) function "our_to_number", and hide the implementation details altogether.

[Tom]

Probably the most clear solution. Thx for making me sure i'm on the right way.

[j0k]

Could you write your comment in english instead of russian?

[Ahmed MANSOUR]

ПЯТЬ ЗНАКОВ ПОСЛЕ ЗАПЯТОЙ ДОСТАТОЧНО = Five decimals are enough.

[default locale]

How does this answer the question?