MySQL Convert Varchar To Int

This is just a quick and simple example on how to convert a varchar column to integer in SQL sorting (order by).

For example, I have an meta key named “post_views_count” in WordPress postmeta table and its value is the total post view for each my article on http://4rapiddev.com (meta_value) and will be counted on every page load for each article.

When I run SQL command via phpmyadmin to get the highest post view list (order by meta_value desc); it returns a list which aren’t my expectation because the meta_value’s data type is varchar; meaning posts have total post view start from 9 will be on the top.

Before convert varchar column to int

1
2
3
4

SELECT * 
FROM `4rd_postmeta` 
WHERE meta_key = 'post_views_count' 
ORDER BY meta_value DESC

Before convert varchar column to int

Convert varchar column to int in MySQL

To fix this issue, I have to convert value of the meta_value column to int before sorting.

1
2
3
4

SELECT * 
FROM `4rd_postmeta` 
WHERE meta_key = 'post_views_count' 
ORDER BY CAST(meta_value AS UNSIGNED) DESC

Convert varchar column to int in MySQL

It looks correct now.