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

i responded to question in ssrs-2008 tag required changing day number in date ordinal number (i.e. "1st", "2nd" instead of "1", "2"). solution involved vb.net function. i'm curious how 1 go performing task in sql (t-sql , sql server in particular), or if there built in support.

so here scenario: have organized footrace 1000 runners , have results in table columns name , place (in normal numbers). want create query display user's name , place in ordinal numbers.

here's scalable solution should work number. thought other's used % 100 11,12,13 mistaken.

with cte_numbers (     select 1 num     union     select num + 1     cte_numbers     num < 1000 )  select  cast(num varchar(10))         +         case             when num % 100 in (11,12,13) 'th' --first checks exception             when num % 10 = 1 'st'             when num % 10 = 2 'nd'             when num % 10 = 3 'rd'             else 'th' --works num % 10 in (4,5,6,7,8,9,0)         end cte_numbers option (maxrecursion 0)