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

Question

I recently answered this question in the SSRS-2008 tag, which demanded that the day number in the date be changed to a serial number (ie, "1st", "2nd" "instead of" 1 "," 2 "). Solution included the VB.Net function, I’m wondering how one could perform this task in SQL (t-sql and SQL Server in particular), or if there is built-in support.

So, here is the scenario: let's say you organized a footrace for 1000 runners and got the results in a table with the Name and Place columns (in regular numbers). You want to create a query that will display the username and its place in serial numbers.

Answer 1

Here's a scalable solution that should work for any number. I thought others used% 100 for 11,12,13, but I was wrong.

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 this as easily in SQL as you can at the application level:

 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 can also put this in a scalar function if necessary.

EDIT

For your example, this 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; 

Answer 3

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 ); 

Answer 4

You can use case, Ie,

UPDATE: Considering teens, as mentioned by TPhe, and a little edited.

 SELECT Name, CASE WHEN Place in(11, 12, 13) then CAST(Place as VARCHAR(20)) + 'th' WHEN RIGHT(CAST(Place as VARCHAR(20)), 1) = '1' then CAST(Place as VARCHAR(20)) + 'st' WHEN RIGHT(CAST(Place as VARCHAR(20)), 1) = '2' then CAST(Place as VARCHAR(20)) + 'nd' WHEN RIGHT(CAST(Place as VARCHAR(20)), 1) = '3' then CAST(Place as VARCHAR(20)) + 'rd' ELSE CAST(Place as VARCHAR(20)) + 'th' END as Place FROM RunnerTable 

Answer 5

  DECLARE @Number int = 94 SELECT CONVERT(VARCHAR(10),@NUMBER) + CASE WHEN @Number % 100 IN (11, 12, 13) THEN 'th' ELSE CASE @Number % 10 WHEN 1 THEN 'st' WHEN 2 THEN 'nd' WHEN 3 THEN 'rd' ELSE 'th' END END 

Answer 6

That would be much better for any number.

  create Function dbo.fn_Numbers_Ordinal (@N as bigint) returns varchar(50) as Begin Declare @a as varchar(50)= CAST(@N AS VARCHAR(50)) return( SELECT CAST(@N AS VARCHAR(50)) + CASE WHEN Right(@a,2)='11' or Right(@a,2)='12' or Right(@a,2)='13' Then 'th' WHEN @N % 10 = 1 THEN 'st' WHEN @N % 10 = 2 THEN 'nd' WHEN @N % 10 = 3 THEN 'rd' ELSE 'th' --for @N % 10 IN (4,5,6,7,8,9,0) END ) end 

Answer 7

 Public Function OrdinalNumberSuffix(ByVal InNumber As Integer) As String Dim StrNumber As String, _ Digit As Byte, _ Suffix As String StrNumber = Trim(Str(InNumber)) If Val(StrNumber) > 3 And Val(StrNumber) < 14 Then Digit = Val(Right(StrNumber, 2)) Else Digit = Val(Right(StrNumber, 1)) End If Select Case Digit Case 1: Suffix = "st" Case 2: Suffix = "nd" Case 3: Suffix = "rd" Case Else: Suffix = "th" End Select OrdinalNumberSuffix = " " & StrNumber & Suffix & " " End Function 

Answer 8

DECLARE @Number int = 113, @Superscript int IF @Number NOT NULL START

 IF LEN(@Number) >= 2 SELECT @Superscript = RIGHT(@Number, 2) ELSE SELECT @Superscript = RIGHT(@Number, 1) SELECT @Number as Number, CASE WHEN @Superscript in (11,12,13) THEN 'th' ELSE CASE WHEN @Superscript = 1 THEN 'st' WHEN @Superscript = 2 THEN 'nd' WHEN @Superscript = 3 THEN 'rd' ELSE 'th' END END as Superscript 

END ELSE SELECT 0 as Number, 'th' as ​​superscript