Order By Split Column

Question

I have a column call it id:

4.1.2.10.0
4.1.2.3.0
4.1.2.4.0
6.1.20.0.0
6.1.3.0.0
...
etc...

What I'd like to do is include an ORDER BY statement that splits the string, to become like this:

4.1.2.3.0
4.1.2.4.0
4.1.2.10.0
6.1.3.0.0
6.1.20.0.0
...
etc....

How would I do something like this?

Brian DeMilia · Accepted Answer · 2015-03-09T03:12:08.377

3

Replace field1 with the actual field name:

select      *
from        tbl
order by    CInt(mid(field1,1,instr(1,field1,'.')-1)),
            CInt(mid(field1,instr(1,field1,'.')+1,instr(2,field1,'.')-1)),
            CInt(mid(field1,instr(3,field1,'.')+1,instr(4,field1,'.')-1)),
            CInt(mid(field1,instr(5,field1,'.')+1,instr(6,field1,'.')-1))

As noted by Gordon in the comments, this is prone to error. May want to await a better answer.

edited Mar 09 '15 at 03:12

answered Mar 09 '15 at 01:58

Brian DeMilia

13,103
1
23
33

You can test w/ this file, link will be valid for 1 month: http://gofile.me/2iedJ/pQDYPiTQ (run query named "Query1", your table is named "tbl") – Brian DeMilia Mar 09 '15 at 02:03
As a note, this will not work for something like "10.10.10.10". – Gordon Linoff Mar 09 '15 at 02:36
1

. . Because the first argument to `instr()` is an offset. Perhaps it's clearer if you consider "10000000.10000000.10000000.1000000". – Gordon Linoff Mar 09 '15 at 02:39
When I wrote this I actually had nth occurrence in mind, as opposed to offset. I rarely use access. As is I think this will work as long as each number within the dots is no more than 2 digits? – Brian DeMilia Mar 09 '15 at 02:48
1

The problem is that the offset error accumulates as you go deeper into the string. – Gordon Linoff Mar 09 '15 at 02:55
I see what you mean re: the compounding effect, I'm not sure there is any way to do this without being able to identify the nth occurrence of the `.` though? I found this custom function but nothing built-in - http://www.access-programmers.co.uk/forums/showthread.php?t=156440 – Brian DeMilia Mar 09 '15 at 03:03
It is possible. You basically have the same problem in SQL Server if you use `charindex()` -- you end up with a bunch of complex nested string functions. Unfortunately, MS Access doesn't have recursive subqueries or XML parsing to help fix this problem. – Gordon Linoff Mar 09 '15 at 03:05

score 1 · Answer 2 · answered Mar 09 '15 at 08:23

As the title suggests split:

Public Function SplitSort( _
    ByVal Value As String, _
    ByVal Element As Integer) _
    As Integer

    On Error Resume Next
    SplitSort = Split(Value, ".")(Element - 1)

End Function

Now:

Order By 
    SplitSort([Fieldname], 1),
    SplitSort([Fieldname], 2),
    SplitSort([Fieldname], 3),
    SplitSort([Fieldname], 4),
    SplitSort([Fieldname], 5)

Order By Split Column

2 Answers2

Linked