469,622 Members | 2,183 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,622 developers. It's quick & easy.

Ordinal Function

Has anyone created a user function to return the ordinal position based
on numeric input?

In other words, f(1) returns 'st', f(2) returns 'nd', f(3) returns
'rd', etc.

Jul 19 '05 #1
4 8354
Terry Coccoli <re*****@ifneeded.com> wrote in message news:<gs***********************@news.easynews.com> ...
Has anyone created a user function to return the ordinal position based
on numeric input?

In other words, f(1) returns 'st', f(2) returns 'nd', f(3) returns
'rd', etc.


Check out the format mask of the to_char function in the sql reference manual.
Other than that (if the format mask is lacking), visit http://asktom.oracle.com
You'll probably find code there.

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #2
Terry Coccoli <re*****@ifneeded.com> wrote in message news:<gs***********************@news.easynews.com> ...
Has anyone created a user function to return the ordinal position based
on numeric input?

In other words, f(1) returns 'st', f(2) returns 'nd', f(3) returns
'rd', etc.


This should work over all integers...

CREATE OR REPLACE
FUNCTION F(N IN NUMBER) RETURN VARCHAR2 IS
X_RESULT VARCHAR2(2);
BEGIN
IF ABS(N) BETWEEN 10 AND 20 THEN
RETURN 'th';
END IF;
SELECT DECODE(MOD(ABS(N),10),1,'st', 2, 'nd', 3, 'rd', 'th')
INTO X_RESULT FROM DUAL;
RETURN X_RESULT;

END;
Jul 19 '05 #3
Terry Coccoli <re*****@ifneeded.com> wrote in message news:<gs***********************@news.easynews.com> ...
Has anyone created a user function to return the ordinal position based
on numeric input?

In other words, f(1) returns 'st', f(2) returns 'nd', f(3) returns
'rd', etc.


Terry, Ordinal position of what? A varchar2 array? The ASCII value?

-- Mark D Powell --
Jul 19 '05 #4
Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...
Terry Coccoli <re*****@ifneeded.com> wrote in message news:<gs***********************@news.easynews.com> ...
Has anyone created a user function to return the ordinal position based
on numeric input?

In other words, f(1) returns 'st', f(2) returns 'nd', f(3) returns
'rd', etc.


Terry, Ordinal position of what? A varchar2 array? The ASCII value?

-- Mark D Powell --


No sooner than I hit post did I realsize that you wanted first,
second, third as in 1st, 2nd, 3rd, etc..., but I use google to access
the newsgroup so I had to wait for the index to be updated to show my
post to add on and by afternoon I had work to do.

You can build a function using the following:

select to_char(trunc(sysdate,'MM') + rownum - 1,'fm DDTH'), rownum
from dba_objects
where rownum < 32
order by 2

Which will produce something like
TO_CH ROWNUM
----- ----------
1ST 1
2ND 2
3RD 3
4TH 4
5TH 5
6TH 6
7TH 7
8TH 8
9TH 9
10TH 10
11TH 11
12TH 12
13TH 13
14TH 14
....

HTH -- Mark D Powell --
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Joseph Numpty | last post: by
2 posts views Thread by stewartfip | last post: by
2 posts views Thread by Mario Pflucker | last post: by
4 posts views Thread by Mark Tarver | last post: by
7 posts views Thread by Dylan Parry | last post: by
3 posts views Thread by Terry Coccoli | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.