Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 109 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How to add ordinality (st, nd, rd, th) to numbers?

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.
Best 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 notnull
; if it is alwaysnull
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 ) ;
Answers
-
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 notnull
; if it is alwaysnull
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 Thank you.
-
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.
-
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.
-
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.
-
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
withinmod( ... , 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 ofmod
suffices. -
All I needed to do was to wrap
p_id
withinmod( ... , 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 ofmod
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.
-
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).