Kotangent

Oracle Numbers to English

by Brian Koser

Oracle has built-in methods for a lot of things. Converting from Arabic to Roman numerals? Check. Generating a byte string containing all valid 1-byte encodings between two given bytes? Sure. Generating ASCII-art llamas from the statistics gathered on crossedition triggers? Well, maybe in 13c.

You can also spell numbers out in English, although I’m not sure if that was an intended use of the functions we’ll be using today. Nevertheless.

The magic that makes it all happen is the Julian date.

This code converts the number to a Julian date (format specifier “J”) then to a string representing the Julian date, which is the number again.

Why convert the number to a date before converting to a string? Well, to_char(string, format) doesn’t accept the format specifiers we’ll need to use; the date version, to_char(date, format), does accept those specifiers, so we first convert to a date before converting to string. And in this case Julian dates are easier to work with because they are stored as a number of days since January 1, 4712 BC. This allows us to easily go from number to date to string, as you saw above.

Caveat: for a number to be converted to a Julian date it will need to be between 1 and 5,373,484 (the highest valid number will have increased by the time you read this). The highest valid number is the number of days since January 1, 4712 BC. If you need to handle 0, negatives, decimals, or numbers larger than about 5 million, you’ll need to write additional code.

Once we have our date, we’ll convert it to the string we want:

Spelled Out

The format specifier, “Jsp”, can be broken down into two parts:

You can also use “JSP” (gives you the string in all caps) and “jsp” (gives you the string in all lower case).

Ordinal

Spelled-Out Ordinals

And if we combine the two, we get spelled-out ordinals.

So get out there and spell some numbers. I’m sure you’re excited to take advantage of your new-found knowledge; if you can’t think of a use for this code, why not add the below to a report?