Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home MySQL MySQL Convert Varchar To Int

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

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

Before convert varchar column to int

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

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

Convert varchar column to int in MySQL

It looks correct now.

Nov 6, 2014Hoan Huynh
Create Simple Math (Mathematical) Captcha With ASP.NET C#C# Read Json From URL And Parse/Deserialize Json
You Might Also Like:
  • MySQL Select Records Created In Last XXX Minutes
  • Change or reset MySQL Root Account Password
  • MySQL Incorrect Data Type Value In Column
  • Where MySQL Data Dir And Innodb Data Home Dir Location
  • Tips For Tuning And Optimizing Mysql On Low Memory Dedicated Server Or VPS
  • Replace String In MySQL
  • Connect Remotely To MySQL or SQL Server Databases for Godaddy Hosting Account
  • Mysql restore database from dump file with GZIP compression
  • Schedule Backup And Zip MySQL Database In Windows
  • Free Mysql Profiler similar to Microsoft SQL Server Profiler – LogMonitor
Hoan Huynh

Hoan Huynh is the founder and head of 4rapiddev.com. Reach him at [email protected]

7 years ago MySQLpost_views_count, UNSIGNED, Wordpress, WordPress postmeta table1,273
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
24,459 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
21,839 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
17,655 views
JQuery Allow only numeric characters or only alphabet characters in textbox
14,994 views
C# Read Json From URL And Parse/Deserialize Json
11,722 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Things to Learn about Installingderm Loan Type S
  • Online Photo Editor – Free Photoediting Software
  • A Guide to Finding the Best Paper Sellers
  • Photoediting in Home Isn’t Hard to Do!

  • Free Photo Editor Online
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (27)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (647)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development