It may happen we need to display the numbers (dollars amounts / values) in words, for instance 1= one, 2000 = two thousand, 1000000 = One Million, etc.

The objective of this post is to provide a way to translate / convert the dollar numbers / values into English words.

You can get the numbers converted into words using Julian Date with some limitation through normal SQL functions. Julian day is the continuous count of days since the beginning of the Julian Period used primarily by astronomers and Julian day number 0 assigned to the day starting at noon on January 1, 4713 BC. For more details on Julian Day, please visit Wiki

For example – SQL

SELECT TO_CHAR (TO_DATE (‘1234’, ‘j’), ‘jsp’) Num2word FROM DUAL;

BO Object:

Result would be “one thousand two hundred thirty-four”

If you look into the above Query / object, 1234 is the number which we want to convert into words, ‘j’ or ‘J’ is the Julian value stored in the database (January 1, 4713 BC).

‘JSP’ (JSP=NOW) as a combined element is the Julian numeric value spelled out in words and return all caps letters.
‘Jsp’ (JSP=NOW) as a combined element is the Julian numeric value spelled out in words and return first letter as caps .
‘jsp’ (JSP=NOW) as a combined element is the Julian numeric value spelled out in words and return all are small letters.

In the above example, 1234 starts from 0 (Julian day number 0 assigned to the day starting at noon on January 1, 4713 BC ) and it will returns “one thousand two hundred thirty-four”.

‘DDSP’ – the numeric date of the month spelled in words and in all caps letters. ‘Ddsp’ – returns first letter as caps and ‘ddsp’ – returns all are small letters.

SELECT TO_CHAR (TO_DATE (‘1’, ‘j’), ‘Ddsp’) Num2word FROM DUAL;

This query will return as “Ten”. How it is been working? Julian Date is 1/1/4713 and when you do TO_DATE(‘1’,’j’), it will take from 1/1/4712 and go backward of that date. ‘Ddsp’ – would return the value from 1 to 31 max based on the calendar dates. In the below table, would show how ‘Ddsp’ returns result as numeric date of the month and spelled in words.

Note: Julian date must be between 1 and 5373484

There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you enter the values more than 5373484, it will throw you an error as shown below:
ORA-01854: julian date must be between 1 and 5373484

To overcome this issue we can create a DB function (Number2Word) with little workaround we can get the desired result.

CREATE OR REPLACE FUNCTION number2word (Val_number IN NUMBER)
RETURN VARCHAR2
AS
TYPE WordArray IS TABLE OF VARCHAR2 (255);

W_str WordArray
:= WordArray (”,
‘ thousand ‘,
‘ million ‘,
‘ billion ‘,
‘ trillion ‘,
‘ quadrillion ‘,
‘ quintillion ‘,
‘ sextillion ‘,
‘ septillion ‘,
‘ octillion ‘,
‘ nonillion ‘,
‘ decillion ‘,
‘ undecillion ‘,
‘ duodecillion ‘);

W_num VARCHAR2 (50) DEFAULT TRUNC (Val_number);
W_return VARCHAR2 (4000);
BEGIN
FOR i IN 1 .. W_str.COUNT
LOOP
EXIT WHEN W_num IS NULL;

IF (SUBSTR (W_num, LENGTH (W_num) – 2, 3) <> 0)
THEN
W_return :=
TO_CHAR (TO_DATE (SUBSTR (W_num, LENGTH (W_num) – 2, 3), ‘J’),
‘Jsp’)
|| W_str (i)
|| W_return;
END IF;

W_num := SUBSTR (W_num, 1, LENGTH (W_num) – 3);
END LOOP;

RETURN W_return;
END;

After you create the above function in DB you can get the any numbers can be converted into words.

SELECT number2word (987654321012345) FROM DUAL;

Nine Hundred Eighty-Seven trillion Six Hundred Fifty-Four billion Three Hundred Twenty-One million Twelve thousand Three Hundred Forty-Five

You can use this Number2Word function to create BO universe object and then convert any numbers in words.

Below is the report data.

In above report, we can see only the whole numbers are converted into words and decimal numbers are omitted in words and also most of the Business reports would display the sales revenue or amounts in Dollars with cents (decimal value).

Create a universe object as below.

Now the report would display the sale revenues in dollars and cents.

Hope this would help you in your projects to display the values with more clarity.