HomePostsGithub

How to order a string column that contains numeric values in SQL?

The Solution

ORDER BY CAST(`column` AS unsigned)

Example

Suppose you have a table called users with the following data:

Table: users
-------------------------------------
| id (integer)  |  sub_id (varchar) |
-------------------------------------
| 1             |  1000             |
| 2             |  500              |
| 3             |  899              |
| 4             |  640              |
-------------------------------------

If you want to order by sub_id, you would use the following query:

SELECT 
		`id`, `sub_id`
FROM 
		`users`
ORDER BY
		CAST(`sub_id` AS unsigned)

This query would return the results:

Table: users
-------------------------------------
| id (integer)  |  sub_id (varchar) |
-------------------------------------
| 2             |  500              |
| 4             |  640              |
| 3             |  899              |
| 1             |  1000             |
-------------------------------------

Additional Info

The CAST() function is support by most major databases, including MySQL, PostgreSQL, and SQL. Please check the documentation for your particular database.

<-- Back to Posts