Sort Numeric & Alphanumeric Accordingly in PHP MySQL

Question

I am using a Query which sorts like;

But I want to get it

What I am using is

SELECT * FROM `sections` ORDER BY CAST(`act` as DECIMAL) ASC

What I exactly want is First Number then Alphanumeric

3 then 3A

It's called natural sorting, mysql does not support this type of sorting, you can use php's natsort() to achieve that. — catcon
– catcon, Commented Oct 19, 2020 at 3:49
what is the datatype for act anyway? normal order by works fine sqlfiddle.com/#!9/50fb55/1 — Kevin
– Kevin, Commented Oct 19, 2020 at 3:50
the datatype is varchar for act because some time it has only numeric and some time alphanumeric — Muhammad Baber Zaman
– Muhammad Baber Zaman, Commented Oct 19, 2020 at 3:58

Dark Knight · Accepted Answer · 2020-10-19 04:37:23Z

1

Considering that you'll always have integer first and then characters in your acts column. Normally this is called Natural Sorting.

Problem Understanding:

Normal sorting works perfectly when we're dealing with variations of a single number i.e. 1, 1A, 1B, 1AB...
Due to string type, it fails when we sort between numbers which starts with same digit like 1, 10, 100....

We can sort in following manner.

SELECT * FROM sections
ORDER BY CAST(acts AS UNSIGNED), acts

Check corresponding fiddle. It is working for MySQL 5.5, 5.6, 5.7 & 8.0.

answered Oct 19, 2020 at 4:37

Dark Knight

6,6211 gold badge22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1