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

Question

I need to convert in a procedure from string to decimal with a fixed decimal separator . independent of culture settings. Further, I know that a decimal number is limited to only six decimal places after . and there is no limit on the number of digits before . . Using the Oracle documentation and its examples for format strings, I now only have this solution:

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

The number of characters 9 to D is the maximum number allowed. I find this format string pretty awful. Is there a better format string for this general conversion or some way to omit the second parameter of the function? In general, I just need to go to the NLS function parameter to say that I just want to convert to decimal separator . , but the second parameter is also required in this case.

Answer 1

You cannot call the to_number function with the third parameter, not the second. I would suggest putting the string of the "ugly" format in the package constant and forget about it.

You can also use dbms_session.set_nls to change your NLS settings and use to_number with no arguments.

Answer 2

Processes both a comma and a period.

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

Answer 3

 CREATE OR REPLACE FUNCTION IS_NUMBER(P_VAR IN VARCHAR2) RETURN NUMBER IS P_NUMBER NUMBER := 0; RIG VARCHAR2(10) := ''; FORMAT VARCHAR2(100) := '999999999999D999999999999'; RES VARCHAR2(100) := ''; BEGIN SELECT VALUE INTO RIG FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS'; IF SUBSTR(RIG,1,1) = '.' THEN RES := REPLACE(P_VAR,',','.'); ELSE RES := REPLACE(P_VAR,'.',','); END IF; P_NUMBER := TO_NUMBER(RES,FORMAT,'NLS_NUMERIC_CHARACTERS='''||RIG||''''); P_NUMBER := ROUND(P_NUMBER,5); --FIVE DIGITS AFTER DECIMAL POINT IS ENOUGH RETURN P_NUMBER; EXCEPTION WHEN OTHERS THEN RETURN -1; END; 

Answer 4

 select to_number(' 12.5 ') + to_number(' 12 ') from dual;