mysql Error sorting number in string including symbols (-, dash)

Question

table: data

column: service_number, type: varchar(11);

select * from data;

service_number
19-1
19-2
20-1
20-10
20-11
20-12
...
20-19
20-2
20-20
20-21
20-22
20-23
20-24
20-3
20-4
20-5
...
20-9

select * from data order by service_number;

select * from data order by service_number *1;

service_number
19-2
19-1
20-9
20-8
20-7
20-6
20-5
20-4
20-3
20-24
20-23
20-22
20-21
20-20
20-2
20-19
20-18
20-17
...
20-10
20-1

The result I want is the same.

19-1
19-2
20-1
20-2
20-3
20-4
20-5
...
20-9
20-10
20-11
20-12
20-13
...
20-19
20-20
20-21
20-22
20-23
20-24

As a result of searching the internet, it was said that if you want to sort by recognizing a string as a number, you can put *1 after the'order by field', but as shown above, it is displayed strangely. I think there is a problem with the (-, dash) symbol, how do I fix it?

"if you want to sort by recognizing a string as a number, you can put *1" — That's just a way to force a cast indirectly (as alternative to doing it [explicitly](https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html)). A simple multiplication cannot do advanced string manipulation. — Álvaro González, May 17 '21 at 08:09
Use the split string solution suggested here: https://stackoverflow.com/a/2696901/1717259 to add the first and second columns to your query as number columns. Then you can sort by these two first and second numbers. — user1717259, May 17 '21 at 08:13

score 1 · Accepted Answer · answered May 17 '21 at 08:15

Just for fun...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(my_bad_data VARCHAR(12) PRIMARY KEY);

INSERT INTO my_table VALUES
('19-1'),
('19-2'),
('20-1'),
('20-2'),
('20-3'),
('20-4'),
('20-5'),
('20-9'),
('20-10'),
('20-11'),
('20-12'),
('20-13'),
('20-19'),
('20-20'),
('20-21'),
('20-22'),
('20-23'),
('20-24');


SELECT * FROM my_table;

+-------------+
| my_bad_data |
+-------------+
| 19-1        |
| 19-2        |
| 20-1        |
| 20-10       |
| 20-11       |
| 20-12       |
| 20-13       |
| 20-19       |
| 20-2        |
| 20-20       |
| 20-21       |
| 20-22       |
| 20-23       |
| 20-24       |
| 20-3        |
| 20-4        |
| 20-5        |
| 20-9        |
+-------------+

SELECT * FROM my_table ORDER BY INET_ATON(REPLACE(my_bad_data,'-','.'));

+-------------+
| my_bad_data |
+-------------+
| 19-1        |
| 19-2        |
| 20-1        |
| 20-2        |
| 20-3        |
| 20-4        |
| 20-5        |
| 20-9        |
| 20-10       |
| 20-11       |
| 20-12       |
| 20-13       |
| 20-19       |
| 20-20       |
| 20-21       |
| 20-22       |
| 20-23       |
| 20-24       |
+-------------+

mysql Error sorting number in string including symbols (-, dash)

1 Answers1