Oracle PL/SQL Programming, 5th Edition

Number Conversions

Computers work with numbers best when those numbers are in some kind 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. You’ll usually perform such conversions using the TO_CHAR and TO_NUMBER functions.

Note

When working with the IEEE-754, binary floating-point types, use TO_BINARY_FLOAT and TO_BINARY_DOUBLE. To simplify the discussion that follows, I’ll generally refer only to TO_NUMBER. Please assume that any unqualified references to TO_NUMBER also apply to the TO_BINARY_FLOAT and TO_BINARY_DOUBLE functions.

The TO_NUMBER Function

The TO_NUMBER function explicitly converts both fixed- and variable-length strings as well as IEEE-754 floating point types to the NUMBER datatype using an optional format mask. Use TO_NUMBER whenever you need to convert character string representations of numbers into their corresponding numeric value. Invoke TO_NUMBER as follows:

TO_NUMBER(string [,format [,nls_params]])

where:

string

Is a string or BINARY_DOUBLE expression containing the representation of a number.

Note

When using TO_BINARY_FLOAT and TO_BINARY_DOUBLE, you may use the strings ‘INF’ and ‘-INF’ to represent positive and negative infinity. You may also use ‘NaN’ to represent “not a number.” These special strings are case-insensitive.

format

Is an optional format mask that specifies ...