In Oracle, how do I convert a number such as 1 to a string such as "1st"?

Question

I'd like to format a number as "1st", "2nd", "4th", "9th", etc. Is there an Oracle function that will do this for me?

Answer 1

Assuming the value supplied is numeric, rather than DATE, you can use TO_CHAR but you have to convert the numeric value to a string, then a DATE (Julian) before ultimately formatting it:

SELECT TO_CHAR(TO_DATE('1', 'dd'), 'ddth')
  FROM DUAL

Result:

01st

When testing, using 'd' for the format didn't return expected results because the value is interpreted as a Julian date. Either substring the output to remove the leading zero, or provide a full date string (doesn't matter to the TO_CHAR because it's only interested in the day of the month):

SELECT TO_CHAR(TO_DATE('1900-01-01', 'YYYY-MM-dd'), 'dth')
  FROM DUAL

Because calendar days end at 31, use the year value instead to handle numbers greater than 31:

SELECT TO_CHAR(TO_DATE('32-01-01', 'YYYY-MM-dd'), 'yyth')
  FROM DUAL

Result:

32nd