Search code examples
mysqlsqlnatural-sort

Pad middle of string with 0s in mysql ORDER BY clause to respect "true" numeric order

In my admin interface I have a listing of subdomains, which follow following logic:

node-k1
node-k2
node-k3
node-k4
node-k5
node-k6
node-k7
node-k8
node-k9
node-k10
node-k11

These are stored in mysql. I need to list them in numeric order, however since I lack leading 0s after node-k, it orders them like so:

node-k1
node-k10
node-k11
node-k2
node-k3
etc

There nodes have different letters, so node-j_ would appear before node-k_. ORDER BY node ASC works fine with the letters obviously, however the numbers are an annoying issue.

Is there any way to make the ORDER BY clause put them in a right order? This is an admin only function, so performance isn't really an issue.

Solution

  • If the number always starts at character 7, then you can cast it to an integer like this:

    SELECT *
FROM tbl
ORDER BY SUBSTR(subdomain, 1, 6), CAST(SUBSTR(subdomain, 7) AS UNSIGNED)

    This orders them first by the prefix, then by the number.