All geek questions in one place

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

I would like to format the number as “1st”, “2nd”, “4th”, “9th”, etc. Is there an Oracle function that will do this for me?

+6
source share
3 answers

Maybe I'm simplifying it, but it looks like the following should work fine (for integers) and is much more readable than converting to a date and back:

select case when initial_extent is null then null when substr(initial_extent,-2,1) = '1' then initial_extent || 'th' else case substr(initial_extent,-1,1) when '1' then initial_extent || 'st' when '2' then initial_extent || 'nd' when '3' then initial_extent || 'rd' else initial_extent || 'th' end end as formatted_number from user_tables
+2
source

Assuming the value entered is numeric rather than DATE, you can use TO_CHAR , but you need to convert the numeric value to a string, then DATE (Julian) before final formatting:

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

Result:

 01st

When testing, using 'd' for the format does not return the expected results, because the value is interpreted as a Julian date. Either adjust the output to remove the initial zero, or specify the full date string (it does not matter for TO_CHAR, because it is only interested in the day of the month):

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

Since calendar days end in 31, use the year value to process numbers greater than 31 instead:

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

Result:

 32nd
+8
source 
 select substr( to_char( to_date( abs( decode( mod( l_value, 10 ), 0, 4, mod( l_value , 10 ) ) ), 'YYYY' ), 'YTH' ), 2 ) as value from dual

Replace l_value with the corresponding, hmmm, value. Must cover any numbers.

-1
source

Source: https://habr.com/ru/post/892366/

More articles:


All Articles