Oracle Database - TO_NUMBER function

> Database > Oracle Database

1 - Syntax

to_number(expr)
to_number(expr,format)
to_number(expr,format,'nls-param')

The format value must have this form: 'dg' where :

  • D is the new decimal character.
  • G is the new group separator.

Example:

999G999D999

nls-param can be one of more NLS parameters as :

3 - Example

SELECT 
  to_number('1,110.123456', '999G999G999D999999','NLS_NUMERIC_CHARACTERS = ''.,''') 
FROM DUAL

4 - How to verify that a character is in the good format number ?

You can either use:

  • the regexp function with the format as regexp value
SELECT REGEXP_INSTR(yourcolumn, '^([0-9]*)$') , yourcolumn
FROM 
  YourTable
WHERE
REGEXP_INSTR(aantal_containers, '^([0-9]{3}[.]{0,1}[0-9]{2})$') = 0
  • or you can create a test function such as:
CREATE OR REPLACE
FUNCTION "IS_NUMBER"("P_VAR" IN VARCHAR2,  "P_FORMAT" IN VARCHAR2 DEFAULT NULL, "P_NLS" IN VARCHAR2 DEFAULT NULL ) 
RETURN NUMBER
IS 
p_number NUMBER; 
BEGIN 
IF (P_Format IS NULL) THEN P_Number := TO_NUMBER(P_Var); 
ELSE p_number := TO_NUMBER(p_var, p_format, p_nls); 
END IF; 
IF (p_number IS NULL) THEN RETURN 0; 
ELSE RETURN 1; 
END IF; 
RETURN ( p_number ); 
EXCEPTION 
WHEN OTHERS THEN RETURN 0; 
END;

that you can use in your predicate:

SELECT yourcolumn
FROM 
  YourTable
WHERE
IS_NUMBER(yourcolumn, '999999D99', 'NLS_NUMERIC_CHARACTERS = ''. ''') = 0
Advertising

5 - Support

5.1 - ORA-06502

If expr cannot be converted into a number, an ORA-06502 is thrown as :

  • ORA-06502: PL/SQL: numeric or value error: character to number conversion error

5.2 - ORA-01722

If to_number is use without its parameters (without the separators for instance), Oracle can thrown a :

  • ORA-01722: invalid number

6 - Documentation / Reference