Archives of the TeradataForum

Message Posted: Fri, 24 Mar 2006 @ 21:39:16 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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'
          ELSE 'th'
      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.

	<Prev	Next>		<<First	<Prev	Next>	Last>>