Solved

Change the sort Order

Posted on 2014-09-17
5
262 Views
Last Modified: 2014-09-17
Hi,

  I have field in a database table and the values are like the following:
As you notice, Unit#1 is followed by Unit#10. Unit#2 came before Unit#20.
I need to sort it based on the last numbers. Is it possible?
I have added both DB2 and SQL Server, as i can modify in either database for my purpose.
Please advise.
Eligibility
Finance#1
Finance#2
Unit#1
Unit#10
Unit#11
Unit#12
Unit#2
Unit#20

Open in new window

0
Comment
Question by:pvsbandi
  • 3
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40327621
but which is the preferred database for this? I think there will be (some) syntax differences.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40327629
SQL Server:
select
*
from theTable
order by
     left(Eligibility, charindex('#',Eligibility))
   , right('000' + right(Eligibility,len(Eligibility) - charindex('#',Eligibility)),3)

Open in new window

Result:
| ELIGIBILITY |
|-------------|
|   Finance#1 |
|   Finance#2 |
|      Unit#1 |
|      Unit#2 |
|     Unit#10 |
|     Unit#11 |
|     Unit#12 |
|     Unit#20 |

Open in new window

It uses # in the string to locate the text to the left
Then it look to the right of that character, and pads that with zeros, finally taking just the last 3 characters.

e.g.
Unit#10
becomes  Unit#
and the 10 becomes 010
0
 

Author Closing Comment

by:pvsbandi
ID: 40327813
Very Slick!
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40327817
Hi pvsbandi,

DB2 doesn't use the charindex function, but the SQL is very similar.  Use locate instead of charindex.  The 2 parameter substr function makes the code a bit easier on the eyes (I wish other vendors would pick this up), and the value will need to be cast as an integer to get around the different string lengths.

select
*
from theTable
order by
     left(Eligibility, locate ('#',Eligibility))
   , cast (substr (Eligibility, locate ('#', Eligibility)+1) as integer)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40327875
pvsbandi, thanks.
keep in mind that IF you get larger "numbers" like #1234 to increase the '00000' and increase the 3 to 4 or 5 etc.

& thanks kdo, my DB2 is awful rusty

but nb: I didn't cast to integer deliberately in case the string isn't numeric
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 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