CODEWITHSUNDEEP
oracle-databaseplsqloracle10gnumber-formatting
Tom
Tom

Reputation: 73

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

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.

Upvotes: 7

Views: 37355

Answers (4)

Jostein Topland
Jostein Topland

Reputation: 1020

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;

Upvotes: 1

Ravindra Vemula...
Ravindra Vemula...

Reputation: 1

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

Upvotes: -4

DENIS
DENIS

Reputation: 9

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;

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67792

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.

Upvotes: 8

Related Questions