How to create ordinal numbers (i.e. "1st" "2nd", etc.) in SQL
Here's a scalable solution that should work for any number. I thought other's used % 100 for 11,12,13 but I was mistaken.
WITH CTE_Numbers
AS
(
SELECT 1 num
UNION ALL
SELECT num + 1
FROM CTE_Numbers
WHERE num < 1000
)
SELECT CAST(num AS VARCHAR(10))
+
CASE
WHEN num % 100 IN (11,12,13) THEN 'th' --first checks for exception
WHEN num % 10 = 1 THEN 'st'
WHEN num % 10 = 2 THEN 'nd'
WHEN num % 10 = 3 THEN 'rd'
ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0)
END
FROM CTE_Numbers
OPTION (MAXRECURSION 0)
You can do that just as easily in SQL as in the app layer:
DECLARE @myDate DATETIME = '2015-05-21';
DECLARE @day INT;
SELECT @day = DAY(@myDate);
SELECT CASE WHEN @day IN ( 11, 12, 13 ) THEN CAST(@day AS VARCHAR(10)) + 'th'
WHEN @day % 10 = 1 THEN CAST(@day AS VARCHAR(10)) + 'st'
WHEN @day % 10 = 2 THEN CAST(@day AS VARCHAR(10)) + 'nd'
WHEN @day % 10 = 3 THEN CAST(@day AS VARCHAR(10)) + 'rd'
ELSE CAST(@day AS VARCHAR(10)) + 'th'
END
You could also put this in a scalar function if necessary.
EDIT
For your example, it would be:
SELECT Name ,
CASE WHEN Place IN ( 11, 12, 13 )
THEN CAST(Place AS VARCHAR(10)) + 'th'
WHEN Place % 10 = 1 THEN CAST(Place AS VARCHAR(10)) + 'st'
WHEN Place % 10 = 2 THEN CAST(Place AS VARCHAR(10)) + 'nd'
WHEN Place % 10 = 3 THEN CAST(Place AS VARCHAR(10)) + 'rd'
ELSE CAST(Place AS VARCHAR(10)) + 'th'
END AS Place
FROM FootRaceResults;
Be very afraid:
with
ArabicRomanConversions as (
select *
from ( values
( 0, '', '', '', '' ), ( 1, 'I', 'X', 'C', 'M' ), ( 2, 'II', 'XX', 'CC', 'MM' ), ( 3, 'III', 'XXX', 'CCC', 'MMM' ), ( 4, 'IV', 'XL', 'CD', '?' ),
( 5, 'V', 'L', 'D', '?' ), ( 6, 'VI', 'LX', 'DC', '?' ), ( 7, 'VII', 'LXX', 'DCC', '?' ), ( 8, 'VIII', 'LXXX', 'DCCC', '?' ), ( 9, 'IX', 'XC', 'CM', '?' )
) as Placeholder ( Arabic, Ones, Tens, Hundreds, Thousands )
),
OrdinalConversions as (
select *
from ( values
( 1, 'st' ), ( 2, 'nd' ), ( 3, 'rd' ), ( 11, 'th' ), ( 12, 'th' ), ( 13, 'th' )
) as Placeholder2 ( Number, Suffix )
),
Numbers as (
select 1 as Number
union all
select Number + 1
from Numbers
where Number < 3999 )
select Number as Arabic,
( select Thousands from ArabicRomanConversions where Arabic = Number / 1000 ) +
( select Hundreds from ArabicRomanConversions where Arabic = Number / 100 % 10 ) +
( select Tens from ArabicRomanConversions where Arabic = Number / 10 % 10 ) +
( select Ones from ArabicRomanConversions where Arabic = Number % 10 ) as Roman,
Cast( Number as VarChar(4) ) + Coalesce( (
select top 1 Suffix from OrdinalConversions where Number = Numbers.Number % 100 or Number = Numbers.Number % 10 order by Number desc ), 'th' ) as Ordinal
from Numbers option ( MaxRecursion 3998 );