?
Solved

Rank display in 1st, 2nd or 3rd

Posted on 2013-11-20
8
Medium Priority
?
495 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

764 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