Generate ordinal numbers in SQL Server

Mehram
 
on Apr 30, 2021 10:39 PM
500 Views

Sir,

Please guid how to generate 1st, 2nd, 3rd in serial till 40th in SQL Server

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 01, 2021 04:33 AM

Hi Mehram,

Refer below query.

SQL

;WITH CTE
AS
(
    SELECT 1 num
    UNION ALL
    SELECT num + 1
    FROM CTE
    WHERE num < 40
)
SELECT CONCAT(CAST(num AS VARCHAR(10)),
       CASE
           WHEN num % 100 IN (11,12,13) THEN 'th'
           WHEN num % 10 = 1 THEN 'st'
           WHEN num % 10 = 2 THEN 'nd'
           WHEN num % 10 = 3 THEN 'rd'
           ELSE 'th'
       END) AS 'Ordinal'
FROM CTE

Output

Ordinal
1st
2nd
3rd
4th
5th
6th
7th
8th
9th
10th
11th
12th
13th
14th
15th
16th
17th
18th
19th
20th
21st
22nd
23rd
24th
25th
26th
27th
28th
29th
30th
31st
32nd
33rd
34th
35th
36th
37th
38th
39th
40th