Listbox alpha numerical sort?

General
1

There a many conversation about listbox sort, but I can’t find one for alpha numerical sort. I would like to sort a column list like SQL sort or sort a column in MS Excel.

Table entries without sort
1
All good things
Xojo
10
5
102
Zulu
23
45
9
1009

Table entries with sort in SQL: select * from tableSort order BY name*1, name

All good things
Xojo
Zulu
1
5
9
10
23
45
102
1009

How can I do this with CompareRow?

2

off the top of my head, based on the COMPAREROWS info in the Lang.Ref…

Function CompareRows(row1 As Integer, row2 As Integer, column As Integer, ByRef result As Integer) As Boolean
dim data1 as string=me.cell(row1,column)
dim data2 as string=me.cell(row2,column)

if not isNumeric(data1) or not isNumeric(data2) then 
 // at least one value is NOT a number
    Return False
else    
    If Val(data1) < Val(data2) Then
      result = -1
    ElseIf Val(data1) > Val(data2) Then
      result = 1
    Else
      result = 0
    End If
    Return True
end if
End Function

This code has NOT been tested, and is offered for illustration purposes only… As such it should not be considered a cut/paste solution, and should be evaluated by each developer to determine if or how it might fit their situation

3

Yes, I know, but this works only if the contents of a cell is only numeric or text. I read some files from a folder like this:
1.png
All good things.png
Xojo.png
10.png
5 All or nothing
102.png
Zulu.png
23.png
45.png
9.png
1009.png

SQL can sort this like this:
All good things.png
Xojo.png
Zulu.png
1.png
5 All or nothing
9.png
10.png
23.png
45.png
102.png
1009.png

4

Use CompareRows as indicated above, but you’ll have to check the contents. You could use Val on those strings, and if two values return the same value, then use a string compare. That would get you closer, but not handle things like 100-100.jpg

dim s1 as String = me.Cell(row1,column) Dim s2 as String = me.cell(row2,column) Dim v1 as double = Cdbl(s1) Dim v2 as Double = Cdbl(s2) If v2<>v1 then Result = Sign(v2-v1) Return True Else if s1 <> s2 then Result = StrComp(s1,s2) Return True End if Return False
which now that I look at it again is nearly identical to what Dave did in less lines…

5

Thanks for your answer. I have create a in-memory sqlite database. Put the file names in database, sort in database and push the sorted file names into ListBox.

6

@Horst Jehle - you could also use a string array, use sortwith and copy to listbox.

7

[quote=381636:@Horst Jehle]
SQL can sort this like this:
All good things.png
Xojo.png
Zulu.png
1.png
5 All or nothing
9.png
10.png
23.png
45.png
102.png
1009.png[/quote]

Ummmm… no, no it doesn’t… SQL will sort “strings” the same way Listbox does normally

create table test (xyz text);
insert into test values('All good things.png');
insert into test values('Xojo.png');
insert into test values('Zulu.png');
insert into test values('1.png');
insert into test values('5 All or nothing');
insert into test values('9.png');
insert into test values('10.png');
insert into test values('23.png');
insert into test values('45.png');
insert into test values('102.png');
insert into test values('1009.png');
SELECT xyz FROM test ORDER by XYZ;

Results

"1.png"
"10.png"
"1009.png"
"102.png"
"23.png"
"45.png"
"5 All or nothing"
"9.png"
"All good things.png"
"Xojo.png"
"Zulu.png"
8

Use this:
SELECT xyz FROM test ORDER by XYZ*1, XYZ;

9

[quote=381695:@Horst Jehle]Use this:
SELECT xyz FROM test ORDER by XYZ*1, XYZ;[/quote]
interesting… and if you think about it, that is almost exactly what the CompareRow suggestion does

FYI… while it works with SQLite, I’d be interested in what a database with strict datatype constraints would do

10

I tested without the , XYZ after *1 and it works too.

I found some MySQL reference about using +0 ordering varchar columns, so I tried this:

SELECT xyz FROM test ORDER by XYZ+0;

and it also works. Maybe using *1 or +0 is the same? Is it better to use it like this?:

SELECT xyz FROM test ORDER by XYZ+0, XYZ;

In what situation the extra , XYZ is needed?

11

you need both xyz*1,xyz to work properly

if the value is NOT number, then xyz*1 = 0 in which case it sorts alphabetically by xyz
if the value does start with a number, then they are sorted by the numerical value, then the alphabetic one

But leading zeros will mess things up, as they will be “ignored”

and if you leave out the “,XYZ” part of ORDER BY
then

will sort in an unknown manner

12

Thank you Dave.

I did some tests and you are right, if I have 5 XYZ and 5 ABC, without “,XYZ” it lists the first record it found that start with 5.

Now the ‘problem’ I found is that if I have extra records (added to the original list above):

01.png 0a.png 0.png

I get:

0.png 0a.png All good things.png Xojo.png Zulu.png 01.png 1.png

instead of:

All good things.png Xojo.png Zulu.png 0.png 0a.png 01.png

Other thing is that ‘A-Z’ go before ‘a-z’. If I add ‘all bad things.png’, I get:

All good things.png Xojo.png Zulu.png all bad things.png

I’m just testing this to learn, I am not working on, or need, a solution with this information. Just a ‘heads up’ to OP that could find issues if using 0 as first character and using upper/lowercase.

13

As I had said… leading ZERO will mess up that “method”
and unless you specify differently… SQL sorts based on CASE, so the results you show are consistent with the methods and data used.

14

Thank you Dave.

I don’t know how to express my ideas in English, sorry for that, I try my best. What I tried to do with my tests and findings is just ‘extra’ information for new people that could think that the “method” will work in every situation. You already said that leading ZERO will mess up things, I just wanted to show what results one gets if the information has leading zero. Then it occurred to me, the original list all 3 text only titles start with uppercase, what could happen if there are lowercase.

Definitely my tests/posts will not help people with experience, but I do hope that a few newbies could find my tests helpful.

15

SELECT * FROM tableSort ORDER BY name*0 ASC, name ASC; does not work! Tested with Valentina Studio Pro 8.1 and Xojo 2018 R1 B15.

Normal order:
SELECT * FROM tableSort ORDER BY name*1 ASC, name ASC;
Reverse order:
SELECT * FROM tableSort ORDER BY name*1 DESC, name DESC;

16

why would you have expected it to “work”… basically

SELECT * FROM tableSort ORDER BY name*0 ASC, name ASC;

is logically the same as

SELECT * FROM tableSort ORDER BY  name ASC;

since *0 sets the first sort argument to be ZERO for ALL records