Re: How to order a string as integer

On 6/28/07, Magnus Borg <magnus@troberg.se> wrote:
>
> Got strings in the format "integer+(char)^0..1", ex 3456, 2323, 2324A,
> 2324B, ...
>
> I want to order them as they where integers, like:
>
> order by cast(STING as unsigned) desc
>
> But when using that query resulting in error 1292. And the result are not
> correct.
>
> How can I remove the chars before i casting the string? And if possible
> take
> the chars in the order.
> So that i get a result like: 1000, 1000A, 1000B, 900, 800, 800A.

The queries you plan to execute influence the database design--there is no way around that.

I would redesign the database in one of three ways:

Possibility #1: Separate the problematic columns into an integer field and a character field, then "order by a, b".

Possibility #2: Add multiple parallel columns (used only for queries). For example, the strings might be "800" and "1000", but the parallel integers are 800 and 1000; ordering by the integers would get the result you want, whereas ordering by the strings would not.

Possibility #3: Keep a single parallel column (probably an integer), mapped such that the sort order is what you want. For example, let's assume you have an unusual sort order such that you want numbers with no letters displayed first, then those suffixed by "C", then all others with letters in order. You could create a "BIGINT" assigned as follows:

a)Take the base part number, multiply by 40.

b)If there is no letter suffix, add nothing.

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

c)If there is a "C" suffix, add 1.

d)If there is an A suffix, add 2.

e)...

So, the part number "800C" would map to (800 * 40) + 1, the part number "800A" would map to (800 * 40) + 2, etc.

Keep those parallel integers maintained each time you INSERT or UPDATE a record.

Then just order by them.

#3 is by far the most powerful approach. If you know in advance what the ordering should be, then you can design the mapping from (part number) -> (integers) to accommodate it. (part number) -> (strings) is also possible if you can make the strings coincide with one of the MySQL collation orders.

In general, even if you could manage to phrase the query you're describing, it could never be efficient. The reason is that MySQL can only keep indexes in terms of the ordinal data types and sort orders that are built-in to give you approximately O(log N) query time. Even if you can do what you want, it won't scale well to large tables.

Do you need more help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

You have to map the problem to the the ordinal data types that MySQL supports (integer, string, float).

Dave. Received on Thu Jun 28 13:54:36 2007