Solved

Rank display in 1st, 2nd or 3rd

Posted on 2013-11-20
8
485 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 65

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 65

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

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.  

Question has a verified solution.

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

Suggested Solutions

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 Backup & Restore 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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

860 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