Solved

Rank display in 1st, 2nd or 3rd

Posted on 2013-11-20
8
490 Views
Last Modified: 2013-11-21
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?
0
Comment
Question by:Mehram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 66

Expert Comment

by:Jim Horn
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 

Open in new window

0
 
LVL 22

Expert Comment

by:Steve Wales
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 

Open in new window


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

Expert Comment

by:Scott Pletcher
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 22

Expert Comment

by:Steve Wales
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 69

Accepted Solution

by:
Scott Pletcher earned 500 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

by:Jim Horn
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 69

Expert Comment

by:Scott Pletcher
ID: 39665923
I certainly don't object.
0
 
LVL 22

Expert Comment

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question