Sorting Mixed Alphanumeric Slugs in MySQL: A Practical Guide for Real-World Applications

When working with identifiers like r1, r20, r100, or smk1, smk50, smk200, it is common to expect them to sort in a natural, human-friendly order. Unfortunately, default MySQL sorting follows pure lexicographical rules, which often leads to unintuitive results such as:

smk1
smk100
smk20

This article explains why this happens, how to fix it properly, and what additional considerations you may need to handle depending on your data structure.

Why Default MySQL Sort Fails for Mixed Slugs

MySQL compares strings character by character. Therefore:

• 's' = 's'
• 'm' = 'm'
• 'k' = 'k'
• '1' < '2' < '9', but '1' is also < '20' because '1' is compared to '2'.

So MySQL thinks:

• smk100 < smk20
  because '1' (from 100) is less than '2' (from 20).

This is valid lexicographically, but wrong for natural numeric expectations.

Correct Approach: Extract and Sort by the Numeric Part

Since your slugs start with a prefix (smk, r, etc.) followed by a number, the solution is simple:

1. Remove the prefix

Use SUBSTRING() to skip the first characters.

2. Convert the remainder to a number

Use CAST(... AS UNSIGNED) for numeric sorting.

3. Order using the numeric value

Sorting Slugs Starting with smk

If all slugs start with 'smk' (3 characters):

SELECT *
FROM `pixie_resources`
WHERE `slug` LIKE 'smk%'
ORDER BY CAST(SUBSTRING(`slug`, 4) AS UNSIGNED);

How it works:

• SUBSTRING(slug, 4) → "smk123" becomes "123"
• CAST(... AS UNSIGNED) ensures numeric ordering
• MySQL sorts naturally: smk1, smk2, smk10, smk100

Sorting Slugs with Other Prefixes (General Pattern)

If your prefix length varies but you know the prefix itself, adjust accordingly:

Prefix: r

ORDER BY CAST(SUBSTRING(slug, 2) AS UNSIGNED)

Prefix: abc

ORDER BY CAST(SUBSTRING(slug, 4) AS UNSIGNED)

Prefix length unknown but alphabetic?

Extract numbers using regex-like logic (MySQL 8+):

ORDER BY REGEXP_SUBSTR(slug, '[0-9]+') + 0;

Handling Edge Cases You May Encounter

1. Slugs without numbers

Example: smk, smkA

These need a fallback:

ORDER BY 
  CAST(REGEXP_SUBSTR(slug, '[0-9]+') AS UNSIGNED),
  slug;

Slugs with no numbers will sort at the top (numeric value = NULL).

2. Slugs with alphanumeric suffixes

Example: smk10a, smk10b

Use numeric-first, then full slug:

ORDER BY 
  CAST(SUBSTRING(slug, 4) AS UNSIGNED),
  slug;

3. Slugs with dashes or separators

Example: smk10-item, smk10-extra

Extract only the numeric part:

ORDER BY 
  CAST(SUBSTRING_INDEX(SUBSTRING(slug, 4), '-', 1) AS UNSIGNED),
  slug;

4. Case sensitivity

If prefixes may contain mixed case (SMK10, smk10):

ORDER BY 
  CAST(SUBSTRING(LOWER(slug), 4) AS UNSIGNED),
  LOWER(slug);

Performance Considerations

Sorting using functions (CAST, SUBSTRING, REGEXP_SUBSTR) makes MySQL unable to use indexes for the ORDER BY step.

If your table is large, consider:

Option A — Add a separate numeric column

Store the extracted number:

Then:

ORDER BY slug_num ASC;

This is the fastest approach.

Option B — Use generated columns (recommended)

ALTER TABLE pixie_resources
ADD slug_num INT GENERATED ALWAYS AS (CAST(SUBSTRING(slug, 4) AS UNSIGNED)) STORED,
ADD INDEX (slug_num);

Now sorting is fully indexed and efficient.

Additional Good Practices

1. Enforce consistent slug patterns

Always use smk<number> or r<number>
Avoid: smk-001, smk_1, SMK1, smk001test

2. Zero-padding is optional

If you ever use zero-padded slugs (smk001), sorting becomes simpler, but readability suffers.

3. Validate slug format on insert

Ensure slugs match a predictable pattern:

^smk[0-9]+$

4. Consider storing structured data

If slugs are meant to be machine-indexable identifiers, keeping numeric and text segments separate improves long-term maintainability.

Finally

Sorting mixed alphanumeric slugs like smk1, smk20, smk100 requires more than a basic ORDER BY.
By extracting and converting the numeric segment, you achieve natural ordering that aligns with human expectations.

The most reliable and maintainable solution is:

SELECT *
FROM `pixie_resources`
WHERE `slug` LIKE 'smk%'
ORDER BY CAST(SUBSTRING(`slug`, 4) AS UNSIGNED);

For large tables or complex formats, consider generated columns, numeric indexes, and slug validations.

Properly handling slug structures not only ensures correct sorting but also improves the long-term scalability and consistency of your data.

Support Us

Share to Friends