If you’ve ever sorted software version numbers like 21.4.0, 21.11.0, or filenames such as file1.txt, file10.txt, and file2.txt, you’ve probably noticed something frustrating.
The results don’t look right.
Instead of ordering values the way humans naturally expect, SQL databases compare strings character by character. As a result, 21.11.0 may appear before 21.4.0, and file10.txt often comes before file2.txt.
This behavior is technically correct—but rarely useful.
ClickHouse® provides the naturalSortKey() function specifically to solve this problem. Although it’s a relatively small utility function, it can save you from writing complicated parsing logic or performing application-side sorting whenever numbers are embedded inside strings.
Understanding the Problem
Most SQL databases perform lexicographic sorting whenever a column is stored as a string.
Lexicographic ordering compares characters from left to right without understanding whether those characters represent numbers.
For example, consider these version numbers:
21.4.0
21.9.0
21.11.0
Enter fullscreen mode Exit fullscreen mode
A standard ORDER BY compares the values one character at a time.
After comparing 21., it compares the next character.
Since "1" comes before "4" and "9" alphabetically, 21.11.0 is considered smaller than both 21.4.0 and 21.9.0.
That produces an order like:
21.11.0
21.4.0
21.9.0
Enter fullscreen mode Exit fullscreen mode
From the database’s perspective, this is perfectly valid.
From a human perspective, it’s clearly incorrect.
Introducing naturalSortKey()
naturalSortKey() transforms a string into a sortable key where numeric portions are compared as numbers instead of plain text.
Instead of sorting directly on the original value, you sort using the generated key.
SELECT s
FROM t
ORDER BY naturalSortKey(s);
Enter fullscreen mode Exit fullscreen mode
The original values remain unchanged.
Only the ordering changes.
The function also has an alias:
NATURAL_SORT_KEY()
Enter fullscreen mode Exit fullscreen mode
Both names behave identically.
A Real Example Using system.functions
ClickHouse® records the version in which every SQL function was introduced inside the system.functions table.
Suppose we want to list all Geo-related functions grouped by their release version.
SELECT
introduced_in,
count()
FROM system.functions
WHERE categories LIKE '%Geo%'
GROUP BY ALL
ORDER BY introduced_in;
Enter fullscreen mode Exit fullscreen mode
Because introduced_in is stored as a string, the versions are sorted lexicographically.
You may see output similar to:
21.11.0
21.4.0
21.9.0
22.1.0
Enter fullscreen mode Exit fullscreen mode
The ordering is technically correct for strings, but it isn’t the chronological order of ClickHouse® releases.
Now replace the ORDER BY clause with naturalSortKey().
SELECT
introduced_in,
count()
FROM system.functions
WHERE categories LIKE '%Geo%'
GROUP BY ALL
ORDER BY naturalSortKey(introduced_in);
Enter fullscreen mode Exit fullscreen mode
The result becomes:
21.4.0
21.9.0
21.11.0
22.1.0
Enter fullscreen mode Exit fullscreen mode
Now the versions appear exactly as users expect.
Sorting Filenames Naturally
Version numbers are only one use case.
The same issue appears with filenames.
Consider the following values:
file1.txt
file10.txt
file2.txt
Enter fullscreen mode Exit fullscreen mode
Standard sorting produces:
file1.txt
file10.txt
file2.txt
Enter fullscreen mode Exit fullscreen mode
Natural sorting instead produces:
file1.txt
file2.txt
file10.txt
Enter fullscreen mode Exit fullscreen mode
Using naturalSortKey() makes this straightforward.
SELECT
arraySort(
x -> naturalSortKey(x),
['file10.txt', 'file2.txt', 'file1.txt']
) AS sorted_files;
Enter fullscreen mode Exit fullscreen mode
Output:
file1.txt
file2.txt
file10.txt
Enter fullscreen mode Exit fullscreen mode
This behavior matches what users typically expect when viewing directories or exported files.
Multiple Numeric Segments
naturalSortKey() doesn’t just work on a single number.
It correctly handles strings containing multiple numeric sections.
Consider these values:
backup-2-part-11
backup-2-part-9
backup-10-part-1
Enter fullscreen mode Exit fullscreen mode
Natural sorting understands every numeric segment independently, producing:
backup-2-part-9
backup-2-part-11
backup-10-part-1
Enter fullscreen mode Exit fullscreen mode
Without naturalSortKey(), lexicographic ordering would produce a much less intuitive result.
Verifying When the Function Was Introduced
One interesting detail about naturalSortKey() is when it actually became available.
The ClickHouse® 26.3 release notes mention the function, leading many users to assume it was introduced in that release.
However, ClickHouse® stores the introduction version of every built-in function inside system.functions.
You can verify this directly.
SELECT
name,
introduced_in
FROM system.functions
WHERE name = 'naturalSortKey';
Enter fullscreen mode Exit fullscreen mode
On current releases, this returns:
naturalSortKey
25.11.0
Enter fullscreen mode Exit fullscreen mode
This indicates that the function actually appeared in version 25.11, while the 26.3 release notes simply highlighted it again.
It’s a useful reminder that ClickHouse®’s own system tables are often the most reliable source when determining exactly when a feature first became available.
Common Use Cases
naturalSortKey() is useful anywhere numbers are embedded inside strings.
Some common examples include:
- Software version numbers
- Semantic versions
- Build identifiers
- Log file names
- Backup snapshots
- Exported report files
- ETL batch numbers
- Chunk identifiers
- Kubernetes pod names
- Object storage file names
Whenever users expect “natural” ordering instead of alphabetical ordering, this function is worth considering.
Performance Considerations
naturalSortKey() computes a transformed sort key for every value during query execution.
For occasional sorting, this overhead is usually negligible.
However, if you’re repeatedly sorting very large datasets using the same natural ordering, repeatedly generating the key can become unnecessarily expensive.
In those situations, consider storing the generated key in a separate materialized column and sorting using that column instead.
Doing so reduces CPU work during query execution while preserving the same ordering behavior.
Things to Keep in Mind
A few points are worth remembering:
-
naturalSortKey()only changes sorting behavior. The original values remain unchanged. - It works with
ORDER BY,arraySort(), and other sorting operations. - It compares numeric portions numerically while leaving non-numeric text unchanged.
- It doesn’t normalize inconsistent version formats such as
v1.2versus1.2.0. - It isn’t intended for case-insensitive or locale-aware string comparisons.
Final Thoughts
naturalSortKey() is one of those utility functions that quietly solves a surprisingly common problem.
Instead of relying on application-side sorting, writing custom parsing logic, or casting portions of strings manually, you can let ClickHouse® produce the ordering users naturally expect with a single function call.
Whether you’re working with software versions, filenames, backup snapshots, build identifiers, or any other strings containing numbers, naturalSortKey() provides a clean and efficient solution that makes your SQL simpler and your results easier to understand.
Small functions like this often have an outsized impact on day-to-day querying, making them well worth adding to your ClickHouse® toolbox.
References
- ClickHouse® Release 26.3 – https://clickhouse.com/blog/clickhouse-release-26-03
- ClickHouse® String Functions –
naturalSortKey()– https://clickhouse.com/docs/sql-reference/functions/string-functions -
system.functionsTable – https://clickhouse.com/docs/operations/system-tables/functions
답글 남기기