Solved

# Rank display in 1st, 2nd or 3rd

Posted on 2013-11-20
Medium Priority
506 Views
Dear Experts,
I have a rank column,
the value of ranks is like this

Rank
------
1
2
3
4
5
so.....

I need to display it in this format

1st
2nd
3rd
so....

0
Question by:Mehram
• 3
• 3
• 2

LVL 66

Expert Comment

ID: 39663671
Something like...
``````SELECT CASE Rank
WHEN 1 THEN '1st'
WHEN 2 THEN '2nd'
WHEN 3 THEN '3rd'
ELSE CAST(Rank as varchar(5)) + 'th' END As the_rank
FROM YourTable
``````
0

LVL 23

Expert Comment

ID: 39663839
Expanding on that a little since there are special rules for 1st vs 11th and what not.

I made everything a string so you can substring things out and test for any length string - so you would need to add code where needed to convert you number to a string (which you need to do anyway if you're adding text to it:

``````select
case when val is null then null
when len(rtrim(ltrim(val))) = 1 then
(case when val = '1' then val+'st'
when val = '2' then val+'nd'
when val = '3' then val+'rd'
else val+'th'
end)
when substring(rtrim(ltrim(val)),len(val)-1,2) = '11' then val+'th'
when substring(rtrim(ltrim(val)),len(val)-1,2) = '12' then val+'th'
when substring(rtrim(ltrim(val)),len(val)-1,2) = '13' then val+'th'
when substring(rtrim(ltrim(val)),len(val),1) = '1' then val+'st'
when substring(rtrim(ltrim(val)),len(val),1) = '2' then val+'nd'
when substring(rtrim(ltrim(val)),len(val),1) = '3' then val+'rd'
else val+'th'
end as the_rank
from
(select '0' as val union
select '1' union select '2' union select '3' union select '4' union
select '10' union select '11' union select '12' union select '13' union select '14' union
select '21' union select '22' union select '23' union select '24' union
select '31' union select '32' union select '33' union select '34' union
select '100' union select '101' union select '102' union select '103' union select '104' union
select '111' union select '112' union select '113' union select '114' union
select '121' union select '122' union select '123' union select '124') a
``````

Run that, you'll see the output seems to meet your requirements.
0

LVL 70

Expert Comment

ID: 39663848
SELECT CAST(Rank as varchar(5)) + CASE
WHEN RANK % 10 = 1 AND RANK <> 11 THEN 'st'
WHEN RANK % 10 = 2 AND RANK <> 12 THEN 'nd'
WHEN RANK % 10 = 3 AND RANK <> 13 THEN 'rd'
ELSE 'th' END AS the_rank
FROM YourTable
0

LVL 23

Expert Comment

ID: 39663861
Scott - your code doesn't account for 11/12/13 - which will end in "th" - otherwise that's very neat.

If you have a more elegant way to deal with it than my mess above, I'd love to see it, just from a learning perspective.
0

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 39663873
Yeah, good catch on the "111", etc.  I think this will do it:

SELECT CAST([Rank] as varchar(5)) + CASE
WHEN [Rank] % 10 = 1 AND [Rank] % 100 <> 11 THEN 'st'
WHEN [Rank] % 10 = 2 AND [Rank] % 100 <> 12 THEN 'nd'
WHEN [Rank] % 10 = 3 AND [Rank] % 100 <> 13 THEN 'rd'
ELSE 'th' END AS the_Rank
FROM dbo.tablename
0

LVL 66

Expert Comment

ID: 39665631
Scott / sjwales - I'm rewriting my article on SQL Server CASE Solutions, would you guys mind if I added the above solution for it, and gave you guys credit?

Thanks.
Jim
0

LVL 70

Expert Comment

ID: 39665923
I certainly don't object.
0

LVL 23

Expert Comment

ID: 39665955
Same here, feel free (although Scott did all the work) :).
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
###### Suggested Courses
Course of the Month13 days, 10 hours left to enroll