Use desktop for interactive SQL sandbox

Sorting Network Line Cards: When Alphabetical Order Fails You

The Backstory

Many years ago, I was building a network topology dashboard. We had chassis with line cards that followed a naming convention: au-10-1 where the first number is the chassis slot and the second is the port.

The product manager asked for a simple list of line cards sorted logically. Just ORDER BY line_card_id and call it a day.

The output looked like this:

au-1-1
au-10-1
au-11-1
au-2-1

"Why is slot 10 showing up before slot 2?" he asked.

Because SQL sorts strings alphabetically. The character '1' comes before '2', so '10' sorts before '2'. Computers are literal like that.

This is the natural sort problem. Humans expect numbers embedded in strings to sort numerically. Databases sort character by character.

Let's fix it.

The Problem

You're managing network equipment inventory. Line cards have IDs like au-10-1 where:

  • au is the card type (not important for sorting)
  • 10 is the chassis slot number
  • 1 is the port number within that slot

Each device might have multiple line cards across different slots:

DROP TABLE IF EXISTS network_topology CASCADE;
CREATE TABLE network_topology (
  id INT PRIMARY KEY,
  device_id INT,
  ip_address VARCHAR(15),
  line_card_id VARCHAR(20)
);

INSERT INTO network_topology VALUES
(1, 10, '10.10.0.1', 'au-10-1'),
(2, 10, '10.10.0.2', 'au-10-2'),
(3, 10, '10.10.1.1', 'au-11-1'),
(4, 10, '10.10.1.2', 'au-11-2'),
(5, 10, '10.10.0.1', 'au-1-1'),
(6, 10, '10.10.0.1', 'au-1-2'),
(7, 10, '10.10.2.1', 'au-2-1'),
(8, 10, '10.10.2.2', 'au-2-2');

You need: Line cards sorted by chassis slot (1, 2, 10, 11), then by port within each slot.

Alphabetical ORDER BY gives you: au-1-1, au-1-2, au-10-1, au-10-2, au-11-1, au-11-2, au-2-1, au-2-2

What you actually want: au-1-1, au-1-2, au-2-1, au-2-2, au-10-1, au-10-2, au-11-1, au-11-2

Let's fix it.

The Solution: Extract and Sort by Numbers

The trick is to pull out the numeric parts and sort them as actual numbers, not strings.

SELECT
  id,
  device_id,
  ip_address,
  line_card_id
FROM network_topology
ORDER BY
  SPLIT_PART(line_card_id, '-', 2)::INTEGER,
  SPLIT_PART(line_card_id, '-', 3)::INTEGER;

Output:

id | device_id | ip_address | line_card_id
---|-----------|------------|-------------
5  | 10        | 10.10.0.1  | au-1-1
6  | 10        | 10.10.0.1  | au-1-2
7  | 10        | 10.10.2.1  | au-2-1
8  | 10        | 10.10.2.2  | au-2-2
1  | 10        | 10.10.0.1  | au-10-1
2  | 10        | 10.10.0.2  | au-10-2
3  | 10        | 10.10.1.1  | au-11-1
4  | 10        | 10.10.1.2  | au-11-2

Perfect. Slot 1, then slot 2, then slot 10, then slot 11. Within each slot, ports are in numeric order.

Breaking Down the Query

Let's unpack that ORDER BY clause.

First sort key (chassis slot):

SPLIT_PART(line_card_id, '-', 2)::INTEGER

How it works:

  1. SPLIT_PART(line_card_id, '-', 2) splits by dash and returns the 2nd part: '10'
  2. ::INTEGER casts the string to an integer: 10

For au-1-2: SPLIT_PART returns '1', cast gives 1.

For au-10-1: SPLIT_PART returns '10', cast gives 10.

Second sort key (port number):

SPLIT_PART(line_card_id, '-', 3)::INTEGER

Same idea - grab the 3rd part (after the second dash) and cast to integer.

For au-10-2: Returns '2', cast gives 2.

Database-Specific Variations

The solution above uses PostgreSQL. Other databases have different string functions.

MySQL / MariaDB:

ORDER BY
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(line_card_id, '-', 2), '-', -1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(line_card_id, '-', -1) AS UNSIGNED);

SUBSTRING_INDEX(str, '-', 2) gets everything up to the 2nd dash. The nested call with -1 grabs everything after the last dash of that result.

Oracle:

ORDER BY 
  TO_NUMBER(REGEXP_SUBSTR(line_card_id, '\d+', 1, 2)),
  TO_NUMBER(REGEXP_SUBSTR(line_card_id, '\d+', 1, 3));

Oracle's REGEXP_SUBSTR with occurrence parameter is powerful. The third argument (1) is start position, fourth argument (2 or 3) is which match to return.

SQL Server:

ORDER BY 
  CAST(PARSENAME(REPLACE(line_card_id, '-', '.'), 2) AS INT),
  CAST(PARSENAME(REPLACE(line_card_id, '-', '.'), 1) AS INT);

SQL Server doesn't have SPLIT_PART, but PARSENAME works on dot-delimited strings. So we replace dashes with dots first.

Alternative Approach: Zero-Padded Sort Keys

If you need this sort order frequently, you can create a computed column that pads the numbers with zeros:

SELECT
  id,
  line_card_id,
  SPLIT_PART(line_card_id, '-', 1) || '-' ||
  LPAD(SPLIT_PART(line_card_id, '-', 2), 3, '0') || '-' ||
  LPAD(SPLIT_PART(line_card_id, '-', 3), 3, '0') AS sort_key
FROM network_topology
ORDER BY sort_key;

Output:

line_card_id | sort_key
-------------|----------
au-1-1       | au-001-001
au-1-2       | au-001-002
au-2-1       | au-002-001
au-2-2       | au-002-002
au-10-1      | au-010-001
au-10-2      | au-010-002

Now alphabetical sorting works because au-001-001 comes before au-010-001.

LPAD(value, 3, '0') pads the value to 3 digits with leading zeros. So '1' becomes '001' and '10' becomes '010'.

When to use this:

  • Sorting happens frequently and you want better performance
  • You can add a generated column or index on the padded value
  • Maximum slot/port numbers are known (you need to know how many digits to pad)

When extraction is fine:

  • Sorting is occasional (no need to pre-compute)
  • Slot numbers could grow beyond your padding width
  • You only need the original format displayed

Challenge 1: Add Card Type to Sort Order

Goal: Sort by chassis slot first, then card type alphabetically, then port number.

Sample data:

au-1-1
au-1-2
bc-1-1
bc-1-2
au-2-1
bc-2-1

Expected output:

au-1-1
bc-1-1
au-1-2
bc-1-2
au-2-1
bc-2-1

Hints:

  1. You already know how to extract the numeric parts
  2. Extract the card type prefix with SPLIT_PART(line_card_id, '-', 1)
  3. Order by: chassis slot (numeric), card type (alphabetic), port (numeric)

Pseudocode:

ORDER BY
  extract_chassis_slot AS INTEGER,
  extract_card_type AS VARCHAR,
  extract_port AS INTEGER

Challenge 2: Find Gaps in Port Assignments

Goal: Find missing port numbers in each chassis slot. If chassis 10 has ports 1, 2, and 5, ports 3 and 4 are gaps.

Expected output:

chassis_slot | missing_port
-------------|-------------
10           | 3
10           | 4

Hints:

  1. Find the min and max port for each chassis slot
  2. Generate a series of numbers from min to max
  3. LEFT JOIN the actual ports to the series
  4. Filter for NULLs (ports in the series but not in the table)

Pseudocode:

WITH port_range AS (
  -- Get min/max port per chassis
),
all_ports AS (
  -- Generate series from min to max
)
SELECT chassis_slot, port_num AS missing_port
FROM all_ports
LEFT JOIN actual_ports ...
WHERE actual_port IS NULL

PostgreSQL has GENERATE_SERIES. MySQL needs a recursive CTE or numbers table.

Real-World Applications

I've used this pattern for:

  1. Network equipment (this example - chassis, line cards, ports)
  2. Building facilities (Floor 1, Floor 2, Floor 10)
  3. Version numbers (v1.2, v1.10, v2.1)
  4. Server names (server-1, server-2, server-10)
  5. Invoice numbers (INV-1, INV-2, INV-10)
  6. Warehouse locations (A-1-1, A-1-2, A-10-1 for aisle-rack-shelf)

Anywhere you embed numbers in identifiers, you'll need this.

The Pattern

When you have identifiers with embedded numbers:

  1. Identify the delimiter (dash, underscore, period, etc.)
  2. Extract each numeric component using string functions
  3. Cast to numeric type (INT, UNSIGNED, etc.)
  4. Sort by the numeric values, not the string
ORDER BY 
  CAST(extract_first_number AS INTEGER),
  CAST(extract_second_number AS INTEGER),
  ...

Different databases, different syntax. Same approach.

Performance Considerations

For large tables (100k+ rows):

  1. Add a generated column with the zero-padded sort key:
ALTER TABLE network_topology
ADD COLUMN sort_key VARCHAR(30)
GENERATED ALWAYS AS (
  LPAD(SPLIT_PART(line_card_id, '-', 2), 3, '0') || '-' ||
  LPAD(SPLIT_PART(line_card_id, '-', 3), 3, '0')
) STORED;

CREATE INDEX idx_sort_key ON network_topology(sort_key);

Now ORDER BY sort_key uses the index instead of extracting and casting for every row.

  1. Or pre-calculate at insert time if your application controls the data:
INSERT INTO network_topology (device_id, ip_address, line_card_id, chassis_slot, port)
VALUES (10, '10.10.0.1', 'au-10-1', 10, 1);

Store the numeric parts separately. Then sorting is trivial.

  1. Use materialized views if you frequently need sorted results:
CREATE MATERIALIZED VIEW sorted_topology AS
SELECT * FROM network_topology
ORDER BY
  SPLIT_PART(line_card_id, '-', 2)::INTEGER,
  SPLIT_PART(line_card_id, '-', 3)::INTEGER;

Refresh periodically. Queries hit the materialized view instead of recomputing the sort.

Try It Yourself

Head to the SQL editor and try these on your own database.

Challenges:

  1. Add a third component to the line card ID (like au-10-1-A) and sort by all parts
  2. Write a query that groups by chassis slot and shows min/max port numbers
  3. Find line cards where the port number is higher than the chassis slot number
  4. Create a report showing how many line cards are in each chassis slot

Advanced:

  • What if card type affects sort order? (e.g., 'primary' cards before 'backup' cards)
  • How would you handle missing components? (e.g., some cards are just au-10 without a port)
  • Can you make it work for variable-length IDs? (2 components vs 3 components in the same table)

Tags: #SQL #StringFunctions #Sorting #NetworkTopology #NaturalSort

Difficulty: Intermediate
Topics: String Manipulation, Type Casting, Sorting, Real-World Data

SQL Output

The Natural Sort Problem
Public
Humans expect 1, 2, 10. Databases give you 1, 10, 2. Learn the pattern for sorting embedded numbers correctly.
jrz1977
PostgreSQL
Created Feb 3, 2026
Contents
1Notes Notes
2SQL SQL
1 SQL 1 Notes
Switch to a SQL tab and press Alt+Enter to run queries
The Natural Sort Problem Updated 2/3/26, 1:13 AM
Notes SQL
Alt+Enter to run