CODEWITHSUNDEEP
mysql
Biprojit Roy
Biprojit Roy

Reputation: 46

How to sort a column with numeric values in MySQL

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"

Upvotes: 1

Views: 114

Answers (1)

ArunKumar M N
ArunKumar M N

Reputation: 1266

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

Upvotes: 3

Related Questions