John John - 1 month ago 5x
SQL Question

How to sort string data that represents numbers

My client has a set of numeric data stored in a string field in a database. So of course it doesn't sort correctly. These rows sort like this:


When they should sort like this:


This is very much a legacy database and I can't change it at all. I also can't change the software that uses the database. The client doesn't own it or have the source code. It has never worked the way they want. However, there is an unused string field that I could use to sort on (only a small number of fields can be sorted on.)

What I would like to do is take the input data, derive a string from it, and store the new string in the unused field, such that when the data is sorted on this new data, the original data sorts correctly, i.e., numerically.

So, for an overly simplistic example, if the algorithm produced the following new data:

105 -> c
3 -> a
44 -> b

Then when the second column was sorted, the first column would look 'correct'.

The tricky bit is that when new rows are added to the database, they must also sort correctly, without having to regenerate the sort data for all rows. This is the part of the problem that has my brain in a twist. I'm not sure it's actually possible.

You can assume that the number will never be more than 5 'digits'.

I realize this is a total kludge, but since I can't change the system, I have to find a work around, rather than a quality solution. Welcome to the real world.

~~~~~~~~~~~~~~~~~~~~~~ S O L U T I O N ~~~~~~~~~~~~~~~~~~

I don't think this is an uncommon problem, so here are the results of Gordon's solution:

mysql> select * from t order by new;
| orig | new |
| 3 | 0000000003 |
| 44 | 0000000044 |
| 105 | 0000000105 |


In most databases, you can just do:

order by cast(col as int)

This will convert the string representation to a number and use that for ordering. There is no need for an additional column. If you add one, I would recommend adding a numeric column to contain the actual value.

If you really want to store something in the unused field, then you can left pad the number. How to do this depends on the database, but here is one typical method:

update t
    set unused = right(concat('0000000000', col), 10);

Not all databases support these two specific functions, but all offer this basic functionality in some method.