Oracle PL/SQL Programming, Third Edition

Number Conversions

Computers work with numbers best when those numbers are in some sort of binary format. We humans, on the other hand, prefer to see our numbers in the form of character strings containing digits, commas, and other punctuation. PL/SQL allows you to convert numbers back and forth between human- and machine-readable form. Most commonly, you’ll perform such conversions using the TO_CHAR and TO_NUMBER functions. In order to use these functions to their fullest extent, you need to understand number format models.

Number Format Models

Number formats are used with both the TO_CHAR and TO_NUMBER functions. You use number formats in calls to TO_CHAR to specify exactly how a numeric value should be translated into a VARCHAR2 string. You can specify the punctuation to use, the location of the positive or negative sign, and other useful items. Conversely, you use number formats in calls to TO_NUMBER to specify how a string representing a numeric value should be interpreted.

A number format mask can comprise one or more elements from Table 9-5. The resulting character string (or the converted numeric value) will reflect the combination of the format model elements that you use. You will find examples of different applications of the format models in the descriptions of both the TO_CHAR and TO_NUMBER functions.

Format elements with a description starting with “Prefix:” can be used only at the beginning of a format mask; when a description starts with “Suffix:”, it can be ...