Type Conversion between NUMBER and CHAR

TO_NUMBER

TO_NUMBER is an Oracle specific function for handling numerical data stored as text, often used to address text-based financial data or user input. By using the TO_NUMBER function, you can explicitly convert character strings to the NUMBER data type, enabling mathematical operations and calculations.

Basic syntax of the function is as follows:

TO_NUMBER(e1, format_model, nls_params);

e1 is an expression that you want to convert into number, which is a required argument for the function. format_model is an optional argument that you can specify the format of the string. For example:

The format_model is comprised of the number format elements listed below:

• Element: ,.
• Description: Commas and decimal points will pass through wherever they are included. Note that only one period is allowed per format mask.
• Example: 9,999,999.99
• Element: $
• Description: Leading dollar sign.
• Example: $999.99
• Element: 0
• Description: Leading and trailing 0.
• Example: 0099.99
• Element: 9
• Description: Any digit.
• Example: 999
• Element: B
• Description: Leading blank for integers.
• Example: B999
• Element: C
• Description: The ISO currency symbol as defined in the NLS_ISO_CURRENCY parameter.
• Example: C999
• Element: D
• Description: Returns the current decimal character as defined by the NLS_NUMERIC_CHARCTERS parameter.
• Example: 999D99
• Element: EEEE
• Description: Returns a value in scientific notation.
• Example: 9.9EEE
• Element: G
• Description: Returns the group separator (e.g., a comma).
• Example: 9G999
• Element: L
• Description: Returns the local currency symbol.
• Example: L999
• Element: MI
• Description: Returns negative value with trailing minus sign; returns positive value with a trailing blank.
• Example: 999MI
• Element: PR
• Description: Returns negative values in angle brackets.
• Example: 999PR
• Element: S (prefix)
• Description: Returns negative values with a leading minus sign, positive values with a leading positive sign. Note that S can appear only in the first position of a format mask.
• Example: S999
• Element: S (suffix)
• Description: Returns negative values with a trailing minus sign, positive values with a trailing positive sign. Note that S can appear only in the last position of a format mask.
• Example: 999S
• Element: TM
• Description: The text minimum number format model returns the smallest number of characters possible.
• Example: TM
• Element: U
• Description: Returns the Euro currency symbol or whatever indicated by the NLS_DUAL_CURRENCY parameter.
• Example: U999
• Element: V
• Description: Returns a value multiplied by 10n, where n is the number of 9s after the V.
• Example: 999V99
• Element: X
• Description: Returns the hexadecimal value.
• Example: XXXX

The TO_NUMBER function also has an optional third parameter, which serves to specify NLS (National Language Support) settings. The parameter allows you to reference any of the three NLS parameters listed below:

• NLS_NUMERIC_CHARACTERS = 'dg'
• 'd' is the decimal character specification and 'g' is the group separator.
• NLS_CURRENCY = 'text'
• 'text' is your specification for local currency symbol.
• NLS_ISO_CURRENCY = 'currency'
• 'currency' is the international currency symbol that you want to specify.

When included, the third parameter for TO_NUMBER is a single string that encompasses one or more of these three NLS parameters. For example:

Unlike in the United States or United Kingdom, in France or Switzerland, comma (,) is used as the decimal separator and period (.) is adopted to separate groups of thousands in large numbers. In the figure shown above, I typed 'NLS_NUMERIC_CHARACTERS = '',.''' to explicitly set the custom separators. Note that I used additional quotation marks surrounding ',.', as it is an escaping character in SQL.

TO_CHAR 

The TO_CHAR function in Oracle SQL is designed to convert data from other types to character. When it is applied to a numeric value, you can explicitly convert the number value into a character string. 

Basic syntax for the TO_CHAR when applied to a number is:

TO_CHAR(n, format_model, nls_params);

Where the parameter n is required numeric value, format_model is optional and comprises one or more format elements, and the nls_parms is optional that corresponds to the same parameter used in the TO_NUMBER function.

In this context, TO_CHAR transforms a numeric value n into a character string. The optional format_model provides guidance on formatting the output, allowing customization with special character, such as dollar signs or other financial symbols, handling of negative numbers, and more. For example: