1

I've got a database with a column that contains the following data:

aaa-1
aaa-2
aaa-3
...
aaa-10
aaa-11
...
aaa-100
aaa-101
...
aaa-1000

When I query and sort the data in ascending order, I get:

aaa-1
aaa-10
aaa-11
...
aaa-100
aaa-101
...
aaa-1000
...
aaa-2
...
aaa-3

Is this actually the correct (machine) way of sorting? Is the order being screwed up because of the aaa- prefix? How do I go about sorting this the way a human would (ie something that looks like the first snippet)?

P.S. If the problem does lie in the prefix, is there a way to remove it and sort with just the numeric component?

P.P.S. It's been suggested to me that I should just change my data and add leading zeroes like aaa-0001 and aaa-0002, etc. However, I'm loathe to go that method as each time the list goes up an order of 10, I'd have to reformat this column.

Thank you all in advance! :)

doraemond
  • 37
  • 1
  • 6

4 Answers4

2

You can extract the number part, convert it to numeric data type and then do an ORDER BY:

SELECT mytable.*,
    CAST(SUBSTRING_INDEX(mycolumn, '-', - 1) AS UNSIGNED) mycolumnintdata
FROM
    mytable
ORDER BY mycolumnintdata;

If there are expressions which does not match number, the CAST function would return 0 and those records would be displayed first. You may handle this separately if needed.

James Jithin
  • 10,183
  • 5
  • 36
  • 51
  • Thanks! This did it for me :) – doraemond Oct 03 '15 at 07:49
  • That's cool @doraemond! Thanks for accepting the answer. Happy programming! – James Jithin Oct 03 '15 at 10:09
  • @James Jithin It works well for me. But unfortunately i need it in a prepared statement to join with my other queries which is already in prepared statement format. I got stuck in the bind_result "Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields..." – Mike Sep 18 '18 at 11:38
  • @James Jithin As I was preparing the code, i actually found the answer. The solution is to add another variable in the bind_result (x,x,x,$mycolumnintdata). Thanks for asking. Without you asking, i wouldn't have found the answer. – Mike Sep 18 '18 at 12:50
1

I had a similar issue and the trick that did it for me was this one

*"ORDER BY LENGTH(column_name), column_name

As long as the non-numeric part of the value is the same length, this will sort 1 before 10, 10 before 100, etc."*

as given by Andreas Bergström on this question.

Hope that helps someone.

Enomatix24
  • 156
  • 1
  • 7
0

I will give you a sample sorting. Not based on your data sample, but this could help you out.

Say you have data like this :

id
----
1
2
6
10
13

when you do ORDER BY id ASC would return :

id
----
1
10
13
2
6

I suggest, use LPAD. This query : SELECT LPAD('12',5,'0') return 00012

So when you have table data like I provide above, you can sort them like this :

SELECT * FROM TABLE
ORDER BY LPAD(ID,7,'0') ASC

Based on your data. SELECT SUBSTR('aaa-100',5,LENGTH('aaa-100') - 3) return 100
So, SELECT LPAD( SUBSTR('aaa-100',5,LENGTH('aaa-100') - 3), 7, '0') return 00000100

So you can combine string function such as SUBSTR and LPAD. Do have any clue now?

Hotdin Gurning
  • 1,821
  • 3
  • 15
  • 24
  • In your example, wouldn't that column sort correctly even without the use of `LDAP` since it's just numerical data? – doraemond Oct 02 '15 at 09:43
  • Yeah, I understand now how LPAD works, thank you! I wonder, though, whether using your method or James' method works faster. – doraemond Oct 03 '15 at 07:52
0

this is the alphabetical order,
you want numerical order,
for do this you must in the ORDER BY clause

  1. trim the costant "aaa-" part

  2. convert it in number

    convert(SUBSTRING(val, 3), integer)

Mattia Caputo
  • 959
  • 1
  • 8
  • 17
  • I see now that the column is a string and is treated as such. Thus, shorter strings will get sorted first. Is this really a limitation of database in that it doesn't really have a deep understanding of the information it contains? – doraemond Oct 03 '15 at 07:49