Archives of the TeradataForum
Message Posted: Fri, 24 Mar 2006 @ 21:39:16 GMT
| Subj: || || Re: Strange request - Ordinal Numbers |
| From: || || Fred W Pluebell |
Is there a particular reason to store the string values? You could use a CASE expression if it's just for display purposes:
CASE WHEN Cardinal MOD 10 = 1 AND Cardinal MOD 100 <> 11 THEN 'st'
WHEN Cardinal MOD 10 = 2 AND Cardinal MOD 100 <> 12 THEN 'nd'
WHEN Cardinal MOD 10 = 3 AND Cardinal MOD 100 <> 13 THEN 'rd'
END as OrdinalSuffix
CAST/TRIM the Cardinal number to a character string and concatenate the suffix expression to get strings as in your example: 1st, 2nd,
3rd, 4th, etc.