Oracle and PL/SQL Recipes: A Problem-Solution Approach

6-1. Converting a String to a Number

Problem

You need to convert some strings into numbers. For instance, your application contains several strings that are entered via a user input screen. These strings need to be converted into numbers so that they can be used to perform calculations.

Solution

Use the TO_NUMBER function to explicitly convert the VARCHAR2 field into a NUMBER. The following examples demonstrate the use of TO_NUMBER by showing how to convert some currency values taken from the user interface into numbers for storage in the database.

The first example demonstrates the conversion of a variable with a datatype of VARCHAR2 into a NUMBER:

DECLARE   in_dollars              VARCHAR2(10) := &dollars;   dollars_formatted  NUMBER; BEGIN ...