Home Page for the TeradataForum

Archives of the TeradataForum

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

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

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'
          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>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020