How to sort a column with numeric values in MySQL

Question

In one of my table column I have values like:

"fol v1.1.2",
"fol v1.2.10",
"fol v1.5.6",
"all fols",
"fol v1.7.10 updated 22.03.2019",
"fol v1.10.11"

After ordering i need the values like:

"all fols",
"fol v1.1.2",
"fol v1.2.10",
"fol v1.5.6",
"fol v1.7.10 updated 22.03.2019",
"fol v1.10.11"

You would need a pretty ugly query to swing this. For best results, I recommend storing the major and minor versions in separate number columns. This would make it very easy to sort the way you want. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 2, 2019 at 4:59

vishal · Accepted Answer · 2019-04-02 05:37:19Z

3

select * from trial order by substring_index(dat,'.',1),cast(substring_index(substring_index(dat,'.',2),'.',-1) as signed),cast(substring_index(substring_index(dat,'.',3),'.',-1) as signed);

what i did is, split the versions in by '.' as a delimiter and then convert into integers then group them according to values.

for more information about splitting refer this w3resource-mysql-substring

edited Apr 2, 2019 at 5:37

vishal

3522 silver badges12 bronze badges

answered Apr 2, 2019 at 5:27

ArunKumar M N

1,2561 gold badge10 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

How to sort a column with numeric values in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related