CODEWITHSUNDEEP
sqlexcelms-access
Jeff
Jeff

Reputation: 33

Natural or Human Sort order

I have been working on this on for months. I just cannot get the natural (True alpha-numeric) results. I am shocked that I cannot get them as I have been able to in RPG since 1992 with EBCDIC.

I am looking for any solution in SQL, VBS or simple excel or access. Here is the data I have:

299-8, 
3410L-87, 
3410L-88, 
420-A20, 
420-A21, 
420A-40, 
4357-3, 
AN3H10A, 
K117GM-8, 
K129-1, 
K129-15, 
K271B-200L, 
K271B-38L, 
K271D-200EL, 
KD1051, 
KD1062, 
KD1092, 
KD1108, 
KD1108, 
M8000-3, 
MS24665-1, 
SK271B-200L, 
SAYA4008

The order I am looking for is the true alpha-numeric order as below:

AN3H10A, 
KD1051, 
KD1062, 
KD1092, 
KD1108, 
KD1108, 
K117GM-8, 
K129-1, 
K129-15, 
MS24665-1,
M8000-3, 
SAYA4008, 
SK271B-200L

The inventory is 7800 records so I have had some problems with processing power as well.

Any help would be appreciated.

Jeff

Upvotes: 0

Views: 3301

Answers (5)

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

There a number of approaches, but likely the least amount of work is to build two columns that split out the delimiter (-) in this case.

You then “pad” the results (spaces, or 0) right justified, and then sort on the two columns.

So in the query builder we have this:

SELECT Field1, 
Format(
Mid(field1,1,IIf(InStr(field1,"-")=0,50,InStr(field1,"-")-1)),
">@@@@@@@@@@") AS Expr1,
Format(
Mid(field1,IIf(InStr(field1,"-")=0,99,InStr(field1,"-")+1)),
">@@@@@@@@@@") AS Expr2
FROM Data

When we run the above raw query we get this:

enter image description here

So now in the query builder, simply sort on the first derived column, and then sort on the 2nd derived column.

Eg this:

enter image description here

Run the query, and we get this result:

enter image description here

Edit:

Looking at you desired results, it looks like above sort is wrong. We have to RIGHT just and pad with 0’s.

So this 2nd try:

SELECT Field1, 
Left(Mid(field1,1,IIf(InStr(field1,"-")=0,30,InStr(field1,"-")-1)) 
& String(30,"0"),30) AS Expr1, 
Left(Mid(field1,IIf(InStr(field1,"-")=0,99,InStr(field1,"-")+1)) 
& String(30,"0"),30) AS Expr2
FROM Data

The results are thus this:

enter image description here

Given your small table size, then the above query should perform quite well.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

In native Excel, you can add multiple sorting columns to return the ASCII code for each character, but if the character is a number, then add a large number to the code (e.g 1000).

Then sort on each of the helper columns, including the first column in the table, but not in the sort.

The formula:

=IFERROR(CODE(MID($A1,COLUMNS($A:A),1))+AND(CODE(MID($A1,COLUMNS($A:A),1))>=48,CODE(MID($A1,COLUMNS($A:A),1))<=57)*1000,"")

The Sort dialog:

enter image description here

The results:

enter image description here

You can implement a similar algorithm using VBA, and probably SQL also. I dunno about VBS or Access.

Upvotes: 3

Kresimir L.
Kresimir L.

Reputation: 2441

Lets say you have your data in column "A". If you put this formula in column "B" =IFERROR(IF(LEFT(A1,1)+1>0,"ZZZZZZZ "&A1,A1),A1), it will automatically add Z in front of all numerical values, so that they will naturally appear after all alphabetical values when you sort A-Z. later you can find&replace that funny ZZZZZZ string...

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Add a sorting column:

, iif (left(fieldname, 1) between '0' and '9', 1, 0) sortField
etc
order by sortField, FieldName

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You could try using format for left padding the string in order by 

select column
from my_table 
order by  Format(column, "0000000000")

Upvotes: 0

Related Questions