• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 921
  • Last Modified:

Sorting the last 2 values in a name

Hello everyone, I am assuming someone has run into this before, I have a bunch of items who's last two characters are -A,-B,-C.....-Z,AA,AB,AC,AD in a data table the problem is any kind of simple sort in the SQL using Order By Right(myname,2)
gives me back -A,AA,AB,AC,AD,-B,-C...-Z) .
Is there a good way of doing what I want through SQL or do I have to go through the whole record-set manually and sort them into an array?
2 Solutions
Depending on the platform...

This would work for Access:

Order By Replace(Right(myname,2),"-","")
Try the following:

ORDER BY LEN(REPLACE(RIGHT(myname, 2), '-', '')), RIGHT(myname, 2)

Open in new window

dsulkarAuthor Commented:
Both of you were right there, I am a programmer, not a full time DBA. I knew there was an easy way. fhlio_admin your answer was complete since just removing the "-" does not change the sort.
Order By Replace(Right(myname,2),"-","") still gave me A,AA,AB,AC,B,C,D...
so the second ORDER RIGHT(myname, 2) was necessary.
Thanks to both of you
mccarlIT Business Systems Analyst / Software DeveloperCommented:
I know the question is already accepted but what DB is this based on? You could also try this... (since the DB mustn't be sorting on the character value, otherwise the '-' WOULD come before 'A', but maybe the sort order that your database is using WILL put the '0' before the 'A')

ORDER BY REPLACE(RIGHT(myname, 2), '-', '0')

Open in new window

If it works it should be more efficient (probably only a very minor difference, but every little bit helps)
dsulkarAuthor Commented:
It is just a standard Access 2003 database, thanks for the tips

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now