6-3. Converting a Number to a String
Problem
You need to alter some numbers into a currency format for display. Given a set of numbers, your application will perform a calculation and then convert the outcome into currency format, which will be a string type.
Solution
Use the TO_CHAR conversion function to obtain a nicely formatted currency string. The following code block accepts a number, performs a calculation, and then converts the number to a string:
CREATE OR REPLACE FUNCTION CALCULATE_BILL(bill_amount IN NUMBER)
RETURN VARCHAR2 AS
tax NUMBER := .12;
tip NUMBER := .2;
total_bill NUMBER := 0;
BEGIN
total_bill := bill_amount + (bill_amount * tax);
total_bill := total_bill + (total_bill * tip);
return to_char(total_bill, '$999.00'),
END;
When a bill amount is passed to the CALCULATE_BILL function, a nicely formatted dollar amount will be returned. If you were to pass 24.75 to the function, it would return $33.26.
How It Works
The TO_CHAR function works much like the other Oracle TO_ conversion functions in that it accepts a number value along with an optional format mask and nls language value. Table 6-3 describes the more commonly used formatting mask characters for numbers.
Table 6-3. Common Formatting Mask Characters
| Character | Description |
| 9 | Represents a numeric character that displays only if a value is present |
| . | Represents a decimal point |
| , | Represents a comma |
| $ | Represents a dollar sign |
| 0 | Represents a numeric character that will always display, even if null |
As you can see from the solution to this recipe, the format mask of $999.00 is chosen. Why not use the mask of $999.99 for the conversion? By using the 0 instead of the 9, you ensure that the cents value will always be present. Even if the cents value is zero, you will still get a .00 at the end of your string. Essentially, the 0 character forces Oracle to pad with zeros rather than spaces.
You can also pad with zero characters to the left of the decimal. Here's an example:
select to_char(82,'0000099') from dual;
That results in the following:
0000082
It is also possible to convert numbers to strings using the CAST function, although TO_CHAR makes for code that is easier to read and maintain. For more information on the use of the CAST function, please see recipe 6-5.