PL/SQL To_Number

The PL/SQL TO_NUMBER function is a powerful conversion function that allows you to convert a character string into a numeric value. It is commonly used when dealing with data types in the Oracle database. The TO_NUMBER function takes a string input and returns a numeric value.

Syntax

The syntax for the TO_NUMBER function is as follows:

TO_NUMBER(input_string, [format_mask], [nls_language])

Let’s explore the different components of the TO_NUMBER function:

input_string: This is the mandatory parameter that represents the character string that you want to convert into a numeric value. It can be a column name, a string literal, or an expression.

format_mask (optional): This parameter allows you to specify the format of the input string. It provides flexibility in handling different input formats. The format mask uses format elements like ‘D’ for decimal separators, ‘0’ for a digit, ‘9’ for a digit or a space, and ‘FM’ for removing leading or trailing spaces.

nls_language (optional): This parameter specifies the language used for converting numbers, currency symbols, and decimal separators. If you omit this parameter, the default language of the session is used.

The TO_NUMBER function returns a numeric value if the conversion is successful. However, if the input string cannot be converted to a number, it will raise an exception. Therefore, it is essential to handle potential conversion errors by using exception handling mechanisms.

Examples

Here are a few examples illustrating the usage of the TO_NUMBER function:

Converting a character string to a numeric value.

DECLARE
  input_string VARCHAR2(10) := '123.45';
  converted_number NUMBER;
BEGIN
  converted_number := TO_NUMBER(input_string);
  DBMS_OUTPUT.PUT_LINE(converted_number);
END;
/

Converting a character string with a specific format mask.

DECLARE
  input_string VARCHAR2(10) := '1,000.50';
  converted_number NUMBER;
BEGIN
  converted_number := TO_NUMBER(input_string, '9G999D99');
  DBMS_OUTPUT.PUT_LINE(converted_number);
END;
/

Converting a character string with a specific NLS language.

DECLARE
  input_string VARCHAR2(10) := '123,45';
  converted_number NUMBER;
BEGIN
  converted_number := TO_NUMBER(input_string, 'FM999G99', 'FRENCH');
  DBMS_OUTPUT.PUT_LINE(converted_number);
END;
/

In the first example, the TO_NUMBER function converts the character string ‘123.45’ into a numeric value. The result, 123.45, is then displayed using the DBMS_OUTPUT.PUT_LINE procedure.

In the second example, a format mask ‘9G999D99’ is used to convert the character string ‘1,000.50’ into a numeric value. The format mask handles the comma (‘,’) as a group separator and the period (‘.’) as a decimal separator.

In the third example, the TO_NUMBER function converts the character string ‘123,45’ into a numeric value, considering the ‘FRENCH’ NLS language. This ensures that the conversion takes into account the appropriate decimal separator based on the specified language.

It is important to note that the TO_NUMBER function may not be able to handle all possible conversions. It has certain limitations, such as the inability to convert strings with non-numeric characters or strings that exceed the maximum length of a number. Therefore, it is crucial to ensure data integrity and handle exceptions appropriately when using the TO_NUMBER function.