sort varchar like 10A5B SQL or PHP

gaucho

Hello all,

I have a problem sorting varchar fields retrieved from a MYSQL-DB. I looked around and didn't find a solution yet, so maybe somebody here might be able to help me.

I have a field of type varchar that stores eihter integer values or a combination of int and string, something like 125-03-01 or 125A23, etc. The problem is that you never know this in advance, so sorting has to treat both types.
When I sort the data by this field, the sort order is always a kind of weird, sure because of string sorting.
Eg:
1
10
11
....
2
20
21

1-03
10-03
11-03
etc.

Now I'm looking for a way to let the field type as varchar, but have sorting to work correctly, doesn't matter if by SQL-statement or a PHP function.
OK, I would prefer a php function because I'm using a db-wrapper-class, and not everything is supported by the different dbms, but the most basic SQL statements.

Any help would be appreciated.

Thanks in advance

Gaucho

tomhath

What you want can be done, but it can get really ugly. One solution is to store the "integers" with blank padding on the right (e.g. ' 1', ..., ' 9', '10', ...). That way they will sort the same as characters and numbers.

Or you can put the padding on in the query using a REGEX and CASE in the ORDER BY.

barand

Take a look at the array function [man]natsort[/man]

gaucho

Hello,

sorry that I didn't reply before, but I was away for a few days.
Thanks for the answers, I'check and tell you.

Regards

Gaucho

gaucho

OK,
I checked natsort(), and this is OK as long as you use a one-dimensional array, as far as I understood.
But my problem is that I have the values to be sorted in multidimensonal arrays, sorry, I didn't mention this before.

This array is like a mirror of the database table.

the multiarray has a counter as mainkey like this
array([1] => Array(..), [2] => Array(..), ....)
the arrays inside contain a datarow from the database, where the keys are the col-names (assoc array).
Now the compare-values or sort-values are within the desc. arrays, one in each.

Best regards

Gaucho

barand

Use a custom sort [man]usort[/man] that uses [man]strnatcmp[/man]