Solved

Change the sort Order

Posted on 2014-09-17
5
265 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:Kent Olsen
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

749 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