Laravel/ PHP: Order By Alphabetical with numbers in order
Stefan Bogdanescu
Founder & Senior Architect · 2026-06-29
# Laravel/PHP: Mastering Alphabetical Sorting with Embedded Numbers
As senior developers working with data, we frequently encounter situations where standard alphabetical sorting, or lexicographical sorting, fails when dealing with strings that contain embedded numbers. This is a classic challenge in data management, especially when dealing with cataloging systems, versioning schemes, or episode listingsâexactly like the example you presented: `S1 Episode 1`, `S1 Episode 11`, etc.
The core issue lies in how computers sort text: they compare characters sequentially. This leads to results that look alphabetically correct but are numerically incorrect when numbers have different digit counts.
## The Pitfall of Lexicographical Sorting
When you use a standard ordering function (like `ORDER BY` in SQL or the default sorting methods in Laravel Eloquent) on strings, it performs a character-by-character comparison.
Consider your examples:
* `S1 Episode 1`
* `S1 Episode 11`
* `S1 Episode 2`
Alphabetically (lexicographically), the sorting process compares them position by position:
1. It compares `S1 Episode ` for all of them (they are equal up to this point).
2. It then compares the next character:
* `S1 Episode **1**`
* `S1 Episode **1**1`
* `S1 Episode **2**`
* `S1 Episode **3**`
Because the character `'1'` comes before `'2'` and `'3'`, the sequence might place `...Episode 11` before `...Episode 2`, which is logically incorrect when you intend to sort by the *numerical value* of the episode number. This demonstrates why relying solely on string sorting for mixed alphanumeric data leads to flawed results.
## The Solution: Implementing Natural Sorting in Laravel
To achieve the desired orderâwhere items are sorted first by the letter sequence and then by the numerical value embedded within the stringâwe need a method known as **Natural Sorting**. Since Eloquent and the underlying database systems don't always offer a built-in "natural sort" function that handles this complex logic perfectly across all SQL dialects, we often need to implement custom sorting logic at the application level.
The most robust way to handle this in Laravel is by leveraging the `DB::raw()` expression to perform custom string manipulation before sorting. We can split the string into its components and explicitly tell the database which parts should be sorted numerically.
### Practical Implementation Example
Let's assume your table has a column named `title` containing strings like `S1 Episode 11`. We need to extract the episode number, treat it as an integer, and sort by that integer.
Here is how you can achieve this using Laravel's Query Builder:
```php
use Illuminate\Support\Facades\DB;
$results = DB::table('your_table')
->select('title', 'episode_number') // Assuming you have a separate numeric column for sorting
->orderBy(DB::raw("LOWER(title)"), 'asc') // Primary sort alphabetically on the full title
->orderBy(DB::raw("CAST(SUBSTRING_INDEX(title, ' ', -1) AS UNSIGNED)"), 'asc') // Secondary sort numerically on the last segment (the episode number)
->get();
// If you only have one column and need to extract the numeric part:
$results = DB::table('your_table')
->select('title')
->orderBy(DB::raw("LOWER(title)"), 'asc') // Sort by the full string alphabetically first
->orderBy(DB::raw("CAST(REGEXP_REPLACE(title, '[^0-9]+', '') AS UNSIGNED)"), 'asc') // Extract only numbers and cast them to integer for true numeric sorting
->get();
```
**Explanation of the Code:**
1. **`LOWER(title)`**: This handles the initial alphabetical sort correctly for all parts of the string.
2. **`CAST(REGEXP_REPLACE(title, '[^0-9]+', '') AS UNSIGNED)`**: This is the crucial part. We use regular expressions (`REGEXP_REPLACE`) to strip out all non-numeric characters (spaces, letters) from the title, leaving only pure digits. Then, we `CAST` these resulting digits into an unsigned integer. This forces the database to sort them numerically rather than lexicographically.
This approach ensures that items are first grouped alphabetically by their identifying prefix (`S1 Episode`) and then correctly ordered numerically by the episode number, solving your ordering problem efficiently within your Laravel application. For more complex data interactions in Laravel projects, understanding these foundational SQL manipulations is key, much like mastering Eloquent relationships. If you are building robust applications, exploring powerful data handling techniques is essential for maintaining data integrity.
## Conclusion
Sorting alphanumeric strings correctly requires moving beyond simple string comparison. By employing a combination of standard alphabetical sorting and custom numerical extraction via database functions (like those used in `DB::raw()`), developers can implement natural sorting logic. This technique transforms an ambiguous string sort into a precise, developer-controlled ordering mechanism, ensuring your data is always presented logically and correctly.