I have a table column which is straightforwards alphanumerical but also can have variations from the norm.

AB1
AB2
AB2a
AB3
AB4
AB11
AB11a

Is how I want to sort it

I tried ORDER BY split_part but it failed because some have an 'a' on the end.

CodePudding user response：

You need to normalise the format of the numeric portion of the text. You can do that by splitting the string into the AB prefix and the numeric part, then left-padding the numeric part to a consistent length with zeroes.

For example: AB11a becomes AB00011a.

Apply this to all the items you've listed and they'll sort in the order you want.

You can do this with

    ... ORDER BY concat(substring(`code`,1,2),lpad(substr(`code`,3),6,'0')) ...

where `code` is the name of the column that contains the data you want to sort.

Note - this assumes that the prefix is always 2 characters.