Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Sorting

Posted on 2015-01-15
Medium Priority
96 Views
Hi EE
I have a simple problem sorting. I have numbers and characters eg H1.
Currently these numbers range from H1 to H50. If I sort H1 H2  H4 H5 H6 H28 and H30 I get
H1
H2
H28
H30
H4
H5
H6
is there a way to be able to display
H1
H2
H4
H5
H6
H28
H30

Any help appreciated

chestera
0
Question by:chestera
[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

LVL 39

Accepted Solution

Pratima Pharande earned 1000 total points
ID: 40552833
try this  T is table names and Field1 is field name

SELECT Field1 from T
order by cint(Mid(Field1,2,2) )
0

Author Comment

ID: 40552894
Pratima Pharande

Looks good thank you. It's late night here  I will try tomorrow

Alan
0

LVL 61

Assisted Solution

mbizup earned 1000 total points
ID: 40553144
The above will work as long as your numeric portion is limited to one or two digits.

This variation simply ignores the H for sorting, so it will handle any number of digits:

``````SELECT Field1 FROM T
ORDER BY Clng(Replace([Field1],"H",""))
``````

And more generically, this will ignore the first character (H or anything else):

``````SELECT Field1 FROM T
ORDER BY Clng(Right([Field1],Len([Field1]) -1)
``````

Also, if you as the developer have any say in naming/numbering conventions like this,  formatting your numbers to be consistent in length will make them automatically sort properly.  You would use leading/padding zeros to make the numeric portions consistent.

For example -  the following, using a single letter prefix followed by three numeric digits will always sort first by letter, and then by number with no SQL gymnastics:

A001
A002
A004
A005
A006
A028
A030
H003
H009
H015
H022
H100
etc...
0

LVL 74

Expert Comment

ID: 40553464
<No Points wanted>
Going even deeper...
If you had even more control over this data.
(For example, you are the developer and you just recently created this table design...)
You could split this field into two, or possibly more, separate fields (The letter portion and the number portion)
Then simply concatenate them back together for "display" purposes.
Then you could always sort by the numeric potion, ...but display the concatenated version.

This might also be useful if you ever needed to add other "segments" to this value...
For example:
H126-A, 400B45, ...etc

Just anther angle to consider in the future...
;-)

JeffCoachman
0

Author Comment

ID: 40554268
Jeffrey Coachman
This was going to be my next move and I might adopt. Thank you for the input

Alan
0

Author Closing Comment

ID: 40554284
Thank you both for you input a great help and appreciated
0

## Featured Post

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month7 days, 17 hours left to enroll