CODEWITHSUNDEEP
mysql
Baya
Baya

Reputation: 15

Order by alphanumeric characters

I've seen several questions regarding this subject, but I cant make much sense of what I'm looking at, so I beg you to forgive me for asking a question that's been asked several times already - I've only started learning all of this sql, php, etc stuff about two weeks ago because I needed to make a tool to help me with my work, so I'm still an absolute begginer :(

Anyway, I'm using mysql and I'm making a query with:

SELECT * FROM table ORDER BY col1

On "col1" I have values which look like:

1/16
2/16
12/16
37/12
74/16
114/13
225/16
etc

The values on this column always follow as "X/YY", where "YY" is ALWAYS two digits, and "X" can range from a single digit to five or six digits. Understandably, as the column is set as varchar in order to accept the "/", when I try to order the values I get something like:

1/16
114/13
12/16
2/16
225/16
37/12
74/16

instead of what I'd want:

37/12
114/13
1/16
2/16
12/16
74/16
225/16

Meaning: first ordering by the two digits on the right, and then the ones on the left of the "/". I understand I could probably do this easily by storing "X" and "YY" on different columns, but I'd really appreciate if someone could show me how to do it as it is.

Best regards, T. F.

Upvotes: 0

Views: 200

Answers (1)

ʰᵈˑ
ʰᵈˑ

Reputation: 11375

By using cast and substring_index, you can sort to get your desired output.

SELECT
    *
FROM
    `37031650`
ORDER BY
    cast(
        substring_index(col1, '/', - 1) AS signed INTEGER
    ),
    cast(
        substring_index(col1, '/', 1) AS signed INTEGER
    ) ASC

Examples

mysql> select * from `37031650`;
+--------+
| col1   |
+--------+
| 1/16   |
| 114/13 |
| 12/16  |
| 2/16   |
| 225/16 |
| 37/12  |
| 74/16  |
+--------+
7 rows in set

mysql> select * from `37031650` order by cast(substring_index(col1,'/', -1) as signed integer), cast(substring_index(col1,'/', 1) as signed integer) asc;
+--------+
| col1   |
+--------+
| 37/12  |
| 114/13 |
| 1/16   |
| 2/16   |
| 12/16  |
| 74/16  |
| 225/16 |
+--------+
7 rows in set

Upvotes: 2

Related Questions