sort-range-in-mysql-devzone

For Sorting data in MySQL table by any column in ascending or descending order, we generally use ORDER BY clause. But what to do, when the data in a column is neither a alphabetic string nor
a numeric value. Here is an example to illustrate this.

Consider a table having a column in which we are having ranges like 0-50, 51-100, 101-250 and so on. and we want to retrieve certain reports based upon this, and for that we need the column to be sorted in
ascending or descending order. Here, in this case, our column data is not numeric and not alphabetic string but a VARCHAR field having some text.

One of the approach to solve this is, to split the range by ( – ) hyphen and then convert the left (or right) string in integer, so that it will be like
0 , 51, 101 , 251, 501 and so on, and then we can use ORDER BY clause.

so to split a string in MySQL, we can use an inbuilt MySQL String function called SUBSTRING_INDEX() and then again we will use
MySQL CONVERT() or CAST() function.

So we will write a query in following manner:

SELECT field_name FROM some_table ORDER BY CONVERT(SUBSTRING_INDEX(field_name,’-‘,1),UNSIGNED INTEGER)

where, SUBSTRING_INDEX(field_name,’-‘,1) will return the string which is in left of ( – ) hyphen, and CONVERT() will convert the left string in an UNSIGNED INTEGER, then we can sort it using ORDER BY clause.

Consider the following example:

this will sort the field in ascending order, and for descending just add DESC at the end, like:

–Sort a range field in Mysql —