Restriction: You can specify only one decimal character in a number format model. Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter.
Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter). Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model). Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number. Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Returns value with a leading dollar sign. Restriction: You can specify only one period in a number format model. Returns a decimal point, which is a period (.) in the specified position. You can specify multiple commas in a number format model.Ī comma element cannot begin a number format model.Ī comma cannot appear to the right of a decimal character or period in a number format model.
Returns a comma in the specified position. Table 3-13 Number Format Elements Element However, if you omit the format model, then Oracle returns either Inf or Nan as a string. If a BINARY_FLOAT or BINARY_DOUBLE value is converted to CHAR or NCHAR, and the input is either infinity or NaN (not a number), then Oracle always returns the pound signs to replace the value. Likewise, if a negative NUMBER value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~). If a positive NUMBER value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. This event typically occurs when you are using TO_CHAR with a restrictive number format string, causing a rounding operation. If a value has more significant digits to the left of the decimal place than are specified in the format, then pound signs (#) replace the value. In the TO_BINARY_FLOAT and TO_BINARY_DOUBLE functions to translate CHAR and VARCHAR2 expressions to BINARY_FLOAT or BINARY_DOUBLE valuesĪll number format models cause the number to be rounded to the specified number of significant digits. In the TO_NUMBER function to translate a value of CHAR or VARCHAR2 data type to NUMBER data type In the TO_CHAR function to translate a value of NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type to VARCHAR2 data type You can use number format models in the following functions: You can change the default date format for your session with the ALTER SESSION statement. For such formats, you can specify the characters returned by these format elements implicitly using the initialization parameter NLS_TERRITORY. The values of some formats are determined by the value of initialization parameters. The number format model for the string ' $2,304.25' is ' $9,999.99'.įor lists of number and datetime format model elements, see Table 3-13, "Number Format Elements" and Table 3-15, "Datetime Format Elements". The datetime format model for the string ' 1' is ' DD-Mon-YYYY'. The datetime format model for the string ' 17:45:29' is ' HH24:MI:SS'. The format for a value you have specified for Oracle to store in the database The format for Oracle to use to return a value from the database In SQL statements, you can use a format model as an argument of the TO_CHAR and TO_DATE functions to specify: When you convert a character string into a date or number, a format model determines how Oracle Database interprets the string. A format model does not change the internal representation of the value in the database. A format model is a character literal that describes the format of datetime or numeric data stored in a character string.