469,622 Members | 2,183 Online

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