Appendix B. Number Format Models

Number formats are used with both the TO_CHAR function and the TO_NUMBER function. 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 B-1. The resulting character string (or the converted numeric value) reflects the combination of the format model elements you use. You will find examples of different applications of the format models in the descriptions of TO_CHAR and TO_NUMBER.

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:”, the element can be used only at the end of a format mask. Most format elements are described in terms of their effect on a conversion of a number to its character string representation. Bear in mind that the majority of such elements may also be used in the converse manner, to specify the format of a character string to be converted into a number.

Table B-1. Number format model elements

Format element



Prefix: puts a dollar sign in front of a number. (for currency symbol, see the C format element).

, (comma)

Places a comma into the return ...

Get Oracle PL/SQL Programming, 5th Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.