Oracle Database - TO_NUMBER function

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 :

• Oracle Database - NLS_NUMERIC_CHARACTERS (decimal separator and thousands separator),
• NLS_CURRENCY,
• NLS_DUAL_CURRENCY
• NLS_ISO_CURRENCY

Articles Related

Example

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

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

Documentation / Reference

• ORACLE - TO_NUMBER, NLS_NUMERIC_CHARACTERS, Decimal character and Group Separator
• to_number [Oracle SQL]