How to add ordinality (st, nd, rd, th) to numbers?

User_QDHXF · Oct 8, 2021 2:48PM

Hello,

Create Table T1_Temp(P_ID number,PC_ID number);

insert into T1_TEMP values (1,null);

insert into T1_TEMP values (2,null);

insert into T1_TEMP values (23,null);

insert into T1_TEMP values (44,null);

Oracle DB Version: Oracle 19c (Enterprise Edition Release 19.0.0.0.0)

I have numbers like:

I am looking for ouput as:

How can I add ordinality (st, nd, rd, th) to numbers on my table? Any help is greatly appreciated.

Thanks.

mathguy · Oct 8, 2021 3:10PM

Here are two ways to do this. One is brute force (coding from scratch). The other takes advantage of the jspth format model for dates. The second solution is easier to code, but probably less efficient. In any case, I wrapped the solution within an outer query to compare the results; the flag will show 'oops!' if the two solutions give different answers. You can run this for numbers up to 1.5 million (say) and select only the rows where the flag is not null; if it is always null then probably both solutions are correct.

I created the data on the fly instead of using your sample, so you can test on all numbers from 1 to <whatever>. I don't show the output - too long - but you can just run it on your own machine.

with
  t1_temp (p_id) as (select level from dual connect by level <= 120)
select p_id, pc_id, pc_id2, case when pc_id != pc_id2 then 'oops!' end as flag
from (
       select p_id,
              to_char(p_id) ||
                case when mod(p_id, 100) between 11 and 19 then 'th'
                     when mod(p_id,  10) = 1               then 'st'
                     when mod(p_id,  10) = 2               then 'nd'
                     when mod(p_id,  10) = 3               then 'rd'
                                                           else 'th' end as pc_id,
              p_id || substr(to_char(to_date(p_id, 'j'), 'jspth'), -2)   as pc_id2
       from   t1_temp
     )
;

mathguy · Oct 8, 2021 3:10PM

Here are two ways to do this. One is brute force (coding from scratch). The other takes advantage of the jspth format model for dates. The second solution is easier to code, but probably less efficient. In any case, I wrapped the solution within an outer query to compare the results; the flag will show 'oops!' if the two solutions give different answers. You can run this for numbers up to 1.5 million (say) and select only the rows where the flag is not null; if it is always null then probably both solutions are correct.

I created the data on the fly instead of using your sample, so you can test on all numbers from 1 to <whatever>. I don't show the output - too long - but you can just run it on your own machine.

with
  t1_temp (p_id) as (select level from dual connect by level <= 120)
select p_id, pc_id, pc_id2, case when pc_id != pc_id2 then 'oops!' end as flag
from (
       select p_id,
              to_char(p_id) ||
                case when mod(p_id, 100) between 11 and 19 then 'th'
                     when mod(p_id,  10) = 1               then 'st'
                     when mod(p_id,  10) = 2               then 'nd'
                     when mod(p_id,  10) = 3               then 'rd'
                                                           else 'th' end as pc_id,
              p_id || substr(to_char(to_date(p_id, 'j'), 'jspth'), -2)   as pc_id2
       from   t1_temp
     )
;

User_QDHXF · Oct 8, 2021 3:13PM

@mathguy Thank you.

mathguy · Oct 8, 2021 3:19PM

Note that the second solution (using date formats) is limited to numbers that represent a Julian date up to 31 December 9999; so a number like six million will result in an error. The first solution is not limited in that way.

It is good to use both solutions, though, at least as verification that the first solution is correct; or, at least, that it doesn't disagree with the second one. If Oracle coded that one wrong, then both solutions are wrong, but in the exact same way.

Solomon Yakobson · Oct 8, 2021 3:50PM

Note that the second solution (using date formats) is limited to numbers that represent a Julian date up to 31 December 9999; so a number like six million will result in an error.

The reason your second solution is limited is not using MOD and using date instead of timestamp:

with t1_temp(p_id)
  as (
      select 0 from dual union all
      select 123456789 from dual
     )
select  p_id,
        p_id || substr(to_char(to_timestamp(to_char(mod(p_id,10)),'ff'),'jspth'),-2) pc_id2
  from  t1_temp
/

      P_ID PC_ID2
---------- ------------------------------------------------
         0 0th
 123456789 123456789th


SQL>

SY.

Solomon Yakobson · Oct 8, 2021 4:01PM

Acually it doesn't matter DATE or TIMESTAMP if using MOD. Without MOD TIMESTAMP FF formst would extend rahbe to 999999999 and will take 0:

with t1_temp(p_id)
  as (
      select 0 from dual union all
      select 999999999 from dual
     )
select  p_id,
        p_id || substr(to_char(to_timestamp(to_char(mod(p_id,10)),'ff'),'jspth'),-2) pc_id2
  from  t1_temp
/

      P_ID PC_ID2
---------- ------------------------------------------------
         0 0th
 999999999 999999999th

SQL>

SY.

mathguy · Oct 8, 2021 4:12PM

@Solomon Yakobson

Your solutions are wrong - they seem to always append th. Should have checked more thoroughly.

with t1_temp(p_id)
  as (
      select  2 from dual union all
      select 33 from dual
     )
select  p_id,
        p_id || substr(to_char(to_timestamp(to_char(mod(p_id,10)),'ff'),'jspth'),-2) pc_id2
  from  t1_temp
/

P_ID PC_ID2    
---- -------
   2 2th                  
  33 33th

The idea is correct though. All I needed to do was to wrap p_id within mod( ... , 100); 100, not 10, to get the correct suffixes for 11, 12, 13 (and 311, 312, 313, etc.) And no need for timestamps; the addition of mod suffices.

Solomon Yakobson · Oct 8, 2021 4:45PM

All I needed to do was to wrap p_id within mod( ... , 100); 100, not 10, to get the correct suffixes for 11, 12, 13 (and 311, 312, 313, etc.) And no need for timestamps; the addition of mod suffices.

And add 100, otherwise Julian date will burp:

with t1_temp(p_id)
 as (
   select 0 from dual union all
   select 1000 from dual
   )
select p_id,
    p_id || substr(to_char(to_date(mod(p_id,10),'j'),'jspth'),-2) pc_id2
 from t1_temp
/
        p_id || substr(to_char(to_date(mod(p_id,10),'j'),'jspth'),-2) pc_id2
                                       *
ERROR at line 7:
ORA-01854: julian date must be between 1 and 5373484

SQL>

With 100:

with t1_temp(p_id)
 as (
   select 0 from dual union all
   select 1000 from dual union all
   select 2 from dual union all
   select 33 from dual
   )
select p_id,
    p_id || substr(to_char(to_date(100 + mod(p_id,10),'j'),'jspth'),-2) pc_id2
 from t1_temp
/

      P_ID PC_ID2
---------- ------------------------------------------------
         0 0th
      1000 1000th
         2 2nd
        33 33rd

SQL>

SY.

mathguy · Oct 8, 2021 5:05PM

Right. I thought incorrectly that the issue would only be with the number 0, but it would actually be with every multiple of 100.

Your addition is 100% correct.

Still I wouldn't use the "Julian trick" other than to check my other solution. I am almost certain that the date function acrobatics waste significant time for no good reason (for this particular task).

Forum Stats

Quick Links

Categories

How to add ordinality (st, nd, rd, th) to numbers?

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

How to add ordinality (st, nd, rd, th) to numbers?

Best Answer

Answers