SQL-Order Strings containing numbers

Question

I have the following values in my table:

a 3
a 5
a 7
aa 5
a 10
b 5

With the ORDER BY command I get the following:

a 10
a 3
a 5
a 7
aa 5
b 5

I want the following result:

a 3
a 5
a 7
aa 5
a 10
b 5

Any ideas how I can solve it in my SQL query?

Fix that. Figure out the ordering criteria. Then get back to us. — Strawberry, Feb 23 '16 at 10:34
What's the order of this sorting? say it in word not in some sample random output. — 1000111, Feb 23 '16 at 10:35
You'll not be able to do that with the data at hand, you'll need to maintain a sort key column for something like this to be pratical. — Allan S. Hansen, Feb 23 '16 at 10:36
you can split the column using something like [this](http://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two) however like what @Strawberry said, you are storing 2 things in a single column. if you fix that, you don't need to use splitting functions which may cause performance issues. — ughai, Feb 23 '16 at 11:29
Welcome to Stack Overflow. With a question like this it's helpful to give a little of the back story. What do these column values represent? Why does `aa 5` come between `a 7` and `a 10` in your desired result? Is the number always preceded by a space? — O. Jones, Feb 23 '16 at 11:32

score 0 · Accepted Answer · answered Feb 23 '16 at 11:36

You can use substring_index() to split data when order:

SELECT
    col1
FROM
    table1 t
ORDER BY
    SUBSTRING_INDEX(col1, ' ', 1),
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ' ', 2),' ',- 1) AS UNSIGNED)

However aa will become after all a as your desired ordering logic is contradicting with data values in both 'columns', one of them must be leading. This query returns:

a 3
a 5
a 7
a 10
aa 5
b 5

SQL-Order Strings containing numbers

1 Answers1

Linked