Rank display in 1st, 2nd or 3rd

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....

Please help how could I do that?
MehramAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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 

Open in new window

0
 
Steve WalesSenior Database AdministratorCommented:
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 

Open in new window


Run that, you'll see the output seems to meet your requirements.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Scott PletcherSenior DBACommented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Scott PletcherSenior DBACommented:
I certainly don't object.
0
 
Steve WalesSenior Database AdministratorCommented:
Same here, feel free (although Scott did all the work) :).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.