Order String as Number in SQL

1. Introduction

Ordering strings as numbers in SQL can be a common requirement, especially when dealing with data that is stored as text but represents numerical values.

In this tutorial, we’ll look at various ways to perform this sorting. We’ll be using MySQL 8, PostgreSQL 16, and SQL Server 2022 for the sample queries.

2. Understanding the Problem

Sometimes, due to various reasons such as data import from external sources or legacy system constraints, a column in a database might be defined as a character type even though it contains only numerical values. When sorting this column using the ORDER BY clause, the results are ordered lexicographically rather than numerically due to its character type. In such cases, where numerical sorting is required, additional methods are needed to ensure the column is sorted numerically.

For example, let’s consider a column with the values:

+----------------+
| Number As Text |
+----------------+
|            100 |
|            -25 |
|             45 |
|            -99 |
+----------------+

When we order this data lexicographically, we get this result:

+----------------+
| Number As Text |
+----------------+
|            -25 |
|            -99 |
|            100 |
|             45 |
+----------------+

However, when sorted numerically, it should return in ascending order:

+----------------+
| Number As Text |
+----------------+
|            -99 |
|            -25 |
|             45 |
|            100 |
+----------------+

We’ll see how to achieve this requirement in PostgreSQL, MySQL, and SQL Server.

We assume that all values are valid numbers and don’t contain any alphanumeric strings.

3. Setup

To write the queries, let’s modify the Exam table from our University database by adding a new column with the VARCHAR datatype:

ALTER TABLE Exam 
ADD attendance_points VARCHAR(10);

Now, let’s update the existing rows with the values we need for this article. We’ll also remove the unnecessary rows to make it easier to demonstrate:

DELETE 
FROM Exam 
WHERE id > 4;
UPDATE Exam SET attendance_points = '100' 
WHERE id=1;
UPDATE Exam SET attendance_points = '-25' 
WHERE id=2;
UPDATE Exam SET attendance_points = '45' 
WHERE id=3;
UPDATE Exam SET attendance_points = '-99' 
WHERE id=4;

In this case, even though the attendance_points values are numeric, the datatype for the column is VARCHAR.

When we perform sorting using the ORDER BY clause, we get the result:

SELECT id, attendance_points FROM Exam
ORDER BY attendance_points; 
+----+-------------------+
| id | attendance_points |
+----+-------------------+
|  2 |               -25 |
|  4 |               -99 |
|  1 |               100 |
|  3 |                45 |
+----+-------------------+

This isn’t the expected order. Instead, we’re expecting the result:

+----+-------------------+
| id | attendance_points |
+----+-------------------+
|  4 |               -99 |
|  2 |               -25 |
|  3 |                45 |
|  1 |               100 |
+----+-------------------+

Here, the attendance_points values are sorted in the ascending order.

4. Using CAST

Most databases provide the CAST function to convert the value from one datatype to another. We can use it and then sort the values in the required order. There might be some syntactical differences in the usage between different databases, but the functionality is generally similar.

4.1. PostgreSQL

Let’s look at the usage of the CAST function in PostgreSQL to order the attendance_points data in ascending order:

SELECT * 
FROM Exam 
ORDER BY CAST(attendace_points AS INT);

The CAST function converts the value from VARCHAR to INT, allowing sorting to be applied numerically. This ensures that the values are ordered as expected in numeric order.

PostgreSQL provides a shorthand for the cast operation using :: operator. Let’s rewrite the previous query:

SELECT * 
FROM Exam 
ORDER BY attendance_points::INT;

This query also returns the result in the expected format.

4.2. MySQL

Let’s look at the usage of the CAST function in MySQL:

SELECT * 
FROM Exam 
ORDER BY CAST(attendance_points AS SIGNED);

The query is quite similar to the PostgreSQL version, but in MySQL, we use the type SIGNED instead of INT. This difference arises from MySQL’s internal design, which restricts CAST operations to a specific subset of types.

4.3. SQL Server

We can use a query similar to that in PostgreSQL to order the results in SQL Server:

SELECT * 
FROM Exam 
ORDER BY CAST(attendance_points AS INT);

This sorts the results in ascending numerical order based on the attendance_points values.

5. Using CONVERT

In this section, let’s look at using the CONVERT function in the ORDER BY clause to sort the values numerically. This function changes the data type of a value, enabling numeric sorting on a VARCHAR column. While it’s supported in MySQL and SQL Server, it’s not supported in PostgreSQL.

5.1. MySQL

We can use the CONVERT function in the MySQL query:

SELECT * 
FROM Exam 
ORDER BY CONVERT(attendance_points, SIGNED);

This sorts the results in the order we expect. If the numbers are all positive, we can use the UNSIGNED datatype instead.

5.2. SQL Server

Now, let’s write the CONVERT query in SQL Server:

SELECT * 
FROM Exam 
ORDER BY CONVERT(INT, attendance_points);

This is quite similar to the one in MySQL, but we can note that the order of arguments for the CONVERT function differs between SQL Server and MySQL.

6. Implicit Conversion

Implicit conversion is a feature in many programming languages and database systems where the system automatically converts data types to facilitate operations. We can apply this in the SQL query to perform the numerical sorting.

Let’s look at the query implementation:

SELECT * 
FROM Exam 
ORDER BY attendance_points+0;

When this operation is executed, the database system automatically converts the string to a number before performing the addition, which ensures that the sorting is done based on numeric values. However, this implicit conversion isn’t universally supported across all databases. While MySQL and SQL Server handle this type of conversion, PostgreSQL doesn’t support it.

7. Conclusion

In this article, we explored different methods for sorting numerical values stored as text in SQL databases.

We started with the CAST function, which works across PostgreSQL, MySQL, and SQL Server. We then looked into the CONVERT function and implicit conversion options available in MySQL and SQL Server. Additionally, we covered PostgreSQL’s shorthand cast operator.

These techniques ensure that numerical values stored as text are sorted correctly, improving data accuracy and usability. The choice of method depends on the specific database system we’re working with.