Table 2-13 Number Format Elements Element
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 datatype to NUMBER datatype In the TO_CHAR function to translate a value of NUMBER, BINARY_FLOAT, or BINARY_DOUBLE datatype to VARCHAR2 datatype 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 2-17, "Matching Character Data and Format Models with the FX Format Model Modifier" and Table 2-19, "Attributes of the XMLFormat Object". 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.