How to create ordinal numbers (i.e. "1st" "2nd", etc.) in SQL

Question

I recently responded to this question in the SSRS-2008 tag that required changing the day number in a date to the ordinal number (i.e. "1st", "2nd" instead of "1", "2"). The solution involved a VB.Net function. I'm curious how one would go about performing this task in SQL (t-sql and SQL Server in particular), or if there is some built in support.

So here is a scenario: say you have organized a footrace for 1000 runners and have the results in a table with the columns Name and Place (in normal numbers). You want to create a query that will display a user's name and their place in ordinal numbers.

Answer 1

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)

Answer 2

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;