MySQL Order By Alpha Numeric Not Working

Question

Here's the case, I use a MySQL query like this

SELECT 
    *
FROM
    tbl_name
WHERE
    id = 1
ORDER BY LENGTH(title), title;

but when there are titles like

Detective Conan Episode 797.5 English Subbed

Detective Conan Episode 829.2 English Subbed

It becomes

Detective Conan Episode 897 English Subbed

Detective Conan Episode 898 English Subbed

Detective Conan Episode 797.5 English Subbed

Detective Conan Episode 829.2 English Subbed

The correct one must be like this

Detective Conan Episode 797.5 English Subbed

Detective Conan Episode 829.2 English Subbed

Detective Conan Episode 897 English Subbed

Detective Conan Episode 898 English Subbed

Is it possible in a MySQL query to sort like what I want? or should I redesign the table structure and split the episode number in a new column.

Thank you.

Aurelien · Answer 1 · 2018-04-26T09:31:36.683

0

I misunderstood your initial request. Try this :

SELECT 
    *
FROM
    tbl_name
WHERE
ORDER BY CAST(title AS UNSIGNED), title;

Check fiddle example here : http://sqlfiddle.com/#!9/755b97/2

Edit : This seems to work only when numbers are at the front.

This seems to be the only working solution for your problem, but at the cost of heavy load : Natural Sort in MySQL

Other solution would be for you to switch to mysql 8.0, which introduces regexp_replace, allowing you to extract the numbers from your string, then ORDER BY CAST(the_result_of_your_regexp). Would still be heavy though.

edited Apr 26 '18 at 09:31

answered Apr 26 '18 at 07:59

Aurelien

1,497
7
15

I edited in the same time you commented. Check it out – Aurelien Apr 26 '18 at 08:02
if use, ORDER BY LENGTH(title) DESC, title; – Viko Wijaya Apr 26 '18 at 08:05
Urahara Episode 10 English Subbed Urahara Episode 11 English Subbed Urahara Episode 12 English Subbed Urahara Episode 1 English Subbed Urahara Episode 2 English Subbed – Viko Wijaya Apr 26 '18 at 08:06
if use ORDER BY LENGTH(title) without desc, it becomes ASC but correct – Viko Wijaya Apr 26 '18 at 08:06

score 0 · Answer 2 · answered Apr 26 '18 at 08:13

0

@Aurelien its working become descending if i use:

SELECT 
    *
FROM
    tbl_name
WHERE
    id = 1
ORDER BY LENGTH(title) DESC, title DESC

but there's still something wrong if the title like in the picture

answered Apr 26 '18 at 08:13

Viko Wijaya

21
5

Remove the DESC on your title. Keep it only on the LENGHT(title). Like in my answer. – Aurelien Apr 26 '18 at 08:21
i try in this stackoverflow not working with the image – Viko Wijaya Apr 26 '18 at 08:22
ORDER BY LENGTH(title) DESC, title DESC the result like this http://tinypic.com/view.php?pic=2804502&s=9#.WuGMw9Nub-Y – Viko Wijaya Apr 26 '18 at 08:25
ORDER BY LENGTH(title) DESC, title; the result like this http://i68.tinypic.com/11jxbmh.png – Viko Wijaya Apr 26 '18 at 08:27
I edited my initial (and most likely final on this post) answer with further info – Aurelien Apr 26 '18 at 17:19

MySQL Order By Alpha Numeric Not Working

2 Answers2

Linked