?
Solved

Rank display in 1st, 2nd or 3rd

Posted on 2013-11-20
8
Medium Priority
?
502 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 23

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 70

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 23

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 70

Accepted Solution

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

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 70

Expert Comment

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

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 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