Peter Larsson Blog

Patron Saint of Lost Yaks

Ordinal your numbers

by Peter Larsson in  , , ,

SELECT        number,
              CASE
                     WHEN number % 100 IN (11, 12, 13) THEN 'th'
                     WHEN number % 10 = 1 THEN 'st'
                     WHEN number % 10 = 2 THEN 'nd'
                     WHEN number % 10 = 3 THEN 'rd'
                     ELSE 'th'
              END AS Ordinal
FROM          master..spt_values
WHERE         type = 'p'
ORDER BY      number

Legacy Comments
Pass the Stripe
2008-10-27		 re: Ordinal your numbers
Well, that's handy! Thanks :)