By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,058 Members | 1,213 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,058 IT Pros & Developers. It's quick & easy.

Ordinal Function

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.