# Thread: numeric sort on an alphanumeric field

1. Registered User
Join Date
Sep 2003
Location
USA
Posts
24

## Unanswered: numeric sort on an alphanumeric field

We have an alphanumeric field whose text/width is in no standard order and our query presents it sorted for user selection e.g., we get:

C203L
C2L 1
D1L
D201L
D2L
DC1L
DC2L

Which should be sorted such as:

C2L 1
C203L
D1L
D2L
D201L
DC1L
DC2L

(This is a list fragment of a very long list)

I am at my wits end as to how can I accomplish this in my query????

Any help at all will be greatly appreciated. Thanks!!!!!

2. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
Is it always

Letter(s) Number(s) Letter(s)

3. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
Rather, is it always one of the following:

Letter(s)

Letter(s) Number(s)

Letter(s) Number(s) Letter(s)

Letter(s) Number(s) Letter(s) Number(s)

etc.

4. Registered User
Join Date
Sep 2003
Location
USA
Posts
24
Pretty much

Letter(s) Number(s) ending in L

the
C2L 1
C2L 2 would be important up to the L

5. Registered User
Join Date
Sep 2003
Location
USA
Posts
24
On second thought, they will probably want the

C2L 1 and
C2L 2 to align this way

but the others are all

letter(s) number(s) ending in L

thanks!

6. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
So, you can parse this identifier into four segments?

Code:
```Segment 1   Segment2   Segment3   Segment4
C                2                L                1
C                203             L
D                1                L
D                2                L
D                201             L
DC              1                L
DC              2                L```
Then do a primary sort on Segment1, a secondary sort on segment2, etc., etc. ?

7. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
The only way you are going to change the sort order is to pull out the part you want to sort by. You will want two fields in the query, one is the actual data and the second is the numeric data from the acutal data so that you results will look like:

C2L 1--------| 2
C203L-------| 203
D1L ---------| 1
D2L ---------| 2
D201L ------|201
DC1L -------|1
DC2L -------|2

The calculated field (2,203,1,2) will be used strictly to sort the data. You don't even need to show it.

I would suggest writing a function that takes the original data and reads one character at a time until you find a number. Then copy all the characters to a variable until you get to an L.

8. Registered User
Join Date
Dec 2003
Posts
268

## Nevermind

I am just posting so I can see how you guys resolve this one. This one sounds pretty interesting. I will toss it around and see if I can come up with anything too.

9. Registered User
Join Date
Dec 2003
Posts
268

## What I came up with

This one did prove to be alittle difficult. I probably spent more time on this than I should have, but it seems to work with the provided records. Test it out and see how it works with a bigger recordset.

Background:

This was done useing DAO, not ADO so you may need to tweek your code a little to make it work.

1. Create a temp table with 4 attributes; segment1, segment2, segment3, segment4
datatypes of text, numeric, text, numeric respectively

2. Create a new module

3. Paste this code into the module

'********************************************
Option Compare Database

Sub buildTable()
Dim db As Database
Dim rs As Recordset
Dim str1, str2, str3, str4 As String
Dim temp As String

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM table1")
rs.MoveFirst
While Not rs.EOF
'clear values
segment = 0
str1 = ""
str2 = ""
str3 = ""
str4 = ""
'store value for next record
temp = rs.Fields(0)
str1 = getSeg(temp, True)
temp = Mid(rs.Fields(0), Len(str1) + 1)
str2 = getSeg(temp, False)
temp = Mid(rs.Fields(0), Len(str2 & str1) + 1)
str3 = getSeg(temp, True)
temp = Mid(rs.Fields(0), Len(str3 & str2 & str1) + 1)
str4 = getSeg(temp, False)
db.Execute ("INSERT INTO Table2 VALUES(" & addquotes(Nz(str1, "")) & "," & IIf(Len(str2) = 0, 0, str2) & ", " & addquotes(Nz(str3, "")) & ", " & IIf(Len(str4) = 0, 0, str4) & ")")
rs.MoveNext
Wend
End Sub
Function getSeg(str As String, Alpha As Boolean) As String
Dim x As Integer
Dim temp As String
Dim tempString As String
If Alpha Then
For x = 1 To Len(str)
temp = Mid(str, x, 1)
If Not (IsNumeric(temp)) Then
tempString = tempString & temp
Else
getSeg = tempString
Exit Function
End If
Next x
Else
For x = 1 To Len(str)
temp = Mid(str, x, 1)
If IsNumeric(temp) Then
tempString = tempString & temp
Else
getSeg = tempString
Exit Function
End If
Next x
End If
'accounts for only 1 char length string
getSeg = tempString
End Function

Function addquotes(str As String) As String
addquotes = Chr(34) & str & Chr(34)
End Function

'**********************************
'end of code

Disclaimer: This probably isn't the most graceful code in the world, but it works There are some redundancies, which I am sure can be hacked out to make it work a little faster. But anyhoo, it works.

Just run the makeTable sub and it should parse out the four segments paste them to a table, then you can sort by the four columns to get the information in an order you want, in this instance, select all four columns and sort in ascending order. Then to join this on another string you can create a trunkated column :
Item: segment1 & segment2 & segment 3 & segment 4

The reason I did it this way was because to try and do the parsing in a query without any type of indicator as to when you should be switching to the next sort option (segment) and the fact the each segment is a dynamic length(1,2, 3, 0) I thought it best to create anew table.

You may also notice an additional function called addquotes. I use this to add "" around strings when passing to a String that is to be used in a SQL statement. You an disregard this but need to make sure that your syntax on the insert is correct.

I hope this works for you. If you come up with a different method or algorithm let me know I would be interested.

10. Registered User
Join Date
Sep 2003
Location
USA
Posts
24
Thank you, I will play with this; I hope to accomplish this without having to create temp tables as tmp table creation is frowned on in the app this is a part of.

The data is coming from a table and being presented to the user in a list box through a query so that they may select it to drive something else.

11. Registered User
Join Date
Dec 2003
Posts
268

## Do it only once

Perhaps you could do it just once and append records to the table whenever you get new items to add. I was thinking about this a little more and came up with a different approach.

doing this type of processing in a query is going to be impossible for two reasons.
1. To dynamically search through a string to pull information out and sort in a column is not doable. Youcan do mid, right, left etc. but to step through each character in a string and verify that the following character is not of the same datatype yo wont be able to do.

2. There is no type of indicator to when the change of the segments were to occur i.e. - or , or somethign like that you do in a query, but seeing as the place for these indicators is it would prove difficult to try and do. Of course you could do it with a rather complicated nested query.

Hope this helps.

One other thing to consider, you coudl append to the new table everytime a new record is created. It woudl not be too difficult to copy the create table sub and turn that into something that can recieve a string, then insert the new record. This will help prevent bloat of the DB, which is what I am assuming the 'people' who frown upon the temp table creation.

following is the revised code.

'********************************************
Option Compare Database

Sub buildTable()
Dim db As Database
Dim rs As Recordset
Dim str1, str3 As String
Dim str2, str4 As Integer
Dim temp As String
Dim i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM table1")
rs.MoveFirst
While Not rs.EOF
'clear values
segment = 0
str1 = ""
str2 = 0
str3 = ""
str4 = 0
'store value for next record
temp = rs.Fields(0)
str1 = getNextString(temp)
temp = Mid(rs.Fields(0), Len(str1) + 1)
str2 = getNextIntString(temp)
temp = Mid(rs.Fields(0), Len(str2 & str1) + 1)
str3 = getNextString(temp)
temp = Mid(rs.Fields(0), Len(str3 & str2 & str1) + 1)
str4 = getNextIntString(temp)
db.Execute ("INSERT INTO Table2 VALUES(" & addquotes(Nz(str1, "")) & "," & str2 & ", " & addquotes(Nz(str3, "")) & ", " & str4 & ")")
rs.MoveNext
Wend
End Sub
Function getNextString(str As String) As String
Dim x As Integer
Dim temp As String
Dim tempString As String
For x = 1 To Len(str)
temp = Mid(str, x, 1)
If Not (IsNumeric(temp)) Then
tempString = tempString & temp
Else
getNextString = tempString
Exit Function
End If
Next x
'accounts for only 1 char length string
getNextString = tempString
End Function

Function getNextIntString(str As String) As Integer
Dim x As Integer
Dim temp As String
Dim tempString As String

For x = 1 To Len(str)
temp = Mid(str, x, 1)
If IsNumeric(temp) Then
tempString = tempString & temp
Else
getNextIntString = CInt(tempString)
Exit Function
End If
Next x
'accounts for only 1 char length string
getNextIntString = CInt(tempString)
End Function
Last edited by mjweyland; 04-01-04 at 13:02.

12. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
"Youcan do mid, right, left etc. but to step through each character in a string and verify that the following character is not of the same datatype yo wont be able to do."

Why not ?

strValue="DC123L2"
LastCharacterIsNumber=0

For i=2 to len(strValue)

If isnumeric(mid\$(strValue,i,1)<>LastCharacterIsNumbe r

LastCharacterIsNumber=abs(LastCharacterIsNumber)-1
... a change has occured, record the postion and do whatever else you want to do

EndIf

13. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
It can be done. When I first read your post I misundertood it. I thought you only need the numeric portion. But you also need the alpha portion at the beginning.

I don't have time to write the functions for you, but all you nned to do is write a function that strips off the alpha portion of the data and returns it. Ex. DC123L2 returns DC or C123L2 returns C. Use is numeric to determine if you gotten to the first numeric.

Then write another function that will return the numeric portion. Skip over any non numerics and when you get to the numerics put that in a string until you get to the L.

The final step is to put a calculated field in your query. In the design grid it would look like:

NumericPortion:GetNumericPortion([FieldValue])

AlphaPortion:GetAlphaPortion([FieldValue])

FieldValue

The data in the query would look like:

DC123L2-------DC-------123

Then just sort the DC and the 123 however you want.

This can also be expanded to each segment if you need to sort with anything after the L.

14. Registered User
Join Date
Dec 2003
Posts
268

## Account for more than one segment

Originally posted by PracticalProgram
"Youcan do mid, right, left etc. but to step through each character in a string and verify that the following character is not of the same datatype yo wont be able to do."

Why not ?

strValue="DC123L2"
LastCharacterIsNumber=0

For i=2 to len(strValue)

If isnumeric(mid\$(strValue,i,1)<>LastCharacterIsNumbe r

LastCharacterIsNumber=abs(LastCharacterIsNumber)-1
... a change has occured, record the postion and do whatever else you want to do

EndIf
That will work if you only need stuff from one end adn the starting point is definitevely 2, how are you going to handle that dynamically for each segment in the string?

i.e.
D1L2
DC1L2
DC101L201
C103l2

for each of these strings the starting points for segment 2, 3 or 4 are all different from eachother. Yet this still needs to be sorted.

If there is a different methodolgy I would love to hear about it.

15. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
To the originator of this posting . . .

as you can see, from the responses, you can accomplish just about anything you need.

There is a tradeoff, however. Going through every character of this field in every record is okay if you have 100 records, or a 1,000 records, or even 10,000 records. But if you have to do this for 50,000 records, then this process is impractical.

The proper thing to do, and it should have been done in the database design phase of this project, was to separate the segments into different fields. Then the sort could be done in a query, rather than in code.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•