Forum Stats

  • 3,768,292 Users
  • 2,252,772 Discussions
  • 7,874,519 Comments

Discussions

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

User_QDHXF
User_QDHXF Member Posts: 20 Green Ribbon

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.

Tagged:

Best Answer

  • mathguy
    mathguy Member Posts: 10,154 Blue Diamond
    edited Oct 8, 2021 3:31PM Accepted Answer

    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

Answers

  • mathguy
    mathguy Member Posts: 10,154 Blue Diamond
    edited Oct 8, 2021 3:31PM Accepted Answer

    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
  • User_QDHXF
    User_QDHXF Member Posts: 20 Green Ribbon
  • mathguy
    mathguy Member Posts: 10,154 Blue Diamond

    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.

    User_QDHXF
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond

    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
    Solomon Yakobson Member Posts: 18,908 Red Diamond

    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
    mathguy Member Posts: 10,154 Blue Diamond

    @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
    Solomon Yakobson Member Posts: 18,908 Red Diamond

    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
    mathguy Member Posts: 10,154 Blue Diamond

    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).