Ordinal Function

[Terry Coccoli]

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.

[sybrandb]

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

[Kevin]

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;

[Mark D Powell]

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

[Mark D Powell]

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