Oracle has built-in meth­ods for a lot of things. Converting from Arabic to Roman nu­mer­als? Check. Generating a byte string con­tain­ing all valid 1-byte en­cod­ings be­tween two given bytes? Sure. Generating ASCII-art lla­mas from the sta­tis­tics gath­ered on crossedi­tion trig­gers? Well, maybe in 13c.

You can also spell num­bers out in English, al­though I’m not sure if that was an in­tended use of the func­tions we’ll be us­ing to­day. Nevertheless.

The magic that makes it all hap­pen is the Julian date.

SELECT to_char(to_date(4321, 'J'), 'J') 
  FROM dual; 
-- result: 0004321

This code con­verts the num­ber to a Julian date (format spec­i­fier “J”) then to a string rep­re­sent­ing the Julian date, which is the num­ber again.

Why con­vert the num­ber to a date be­fore con­vert­ing to a string? Well, to_char(string, format) does­n’t ac­cept the for­mat spec­i­fiers we’ll need to use; the date ver­sion, to_char(date, format), does ac­cept those spec­i­fiers, so we first con­vert to a date be­fore con­vert­ing to string. And in this case Julian dates are eas­ier to work with be­cause they are stored as a num­ber of days since January 1, 4712 BC. This al­lows us to eas­ily go from num­ber to date to string, as you saw above.

Caveat: for a num­ber to be con­verted to a Julian date it will need to be be­tween 1 and 5,373,484 (the high­est valid num­ber will have in­creased by the time you read this). The high­est valid num­ber is the num­ber of days since January 1, 4712 BC. If you need to han­dle 0, neg­a­tives, dec­i­mals, or num­bers larger than about 5 mil­lion, you’ll need to write ad­di­tional code.

Once we have our date, we’ll con­vert it to the string we want:

Spelled Out

SELECT to_char(to_date(4321, 'J'), 'Jsp') 
  FROM dual;
-- result: Four Thousand Three Hundred Twenty-One

The for­mat spec­i­fier, “Jsp”, can be bro­ken down into two parts:

  • “J” gives us a Julian date
  • “sp” (short for “spelling”) con­verts the num­ber to English

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

Ordinal

SELECT to_char(to_date(4321, 'J'), 'fmJth') 
  FROM dual;
-- result: 4321st
  • “fm” re­moves lead­ing ze­roes
  • “J” gives us a Julian date
  • “th” adds the or­di­nal suf­fix, so we can get 1st, 2nd, 8th, etc.
    • I as­sume that “th” is used be­cause most or­di­nals end in “th”.

Spelled-Out Ordinals

SELECT to_char(to_date(4321, 'J'), 'JspTH') 
  FROM dual; 
-- Result: Four Thousand Three Hundred Twenty-First

And if we com­bine the two, we get spelled-out or­di­nals.

So get out there and spell some num­bers. I’m sure you’re ex­cited to take ad­van­tage of your new-found knowl­edge; if you can’t think of a use for this code, why not add the be­low to a re­port?

SELECT CASE 
       WHEN trunc(SYSDATE) = to_date('04-01', 'MM-DD') THEN 
         to_char(to_date(reportcount, 'J'), 'Jsp') 
       ELSE reportcount 
       END AS reportcount 
  FROM ...