Mysql Sorting mixed strings of letters and numbers, letters before numbers

Question

I have a varchar field that I need to sort alphanumerically but any letter must sort before all numbers. So any time a letter is sorted against a number anywhere in the string it must come first. So for instance

11B22

must sort before

I have tried this solution: ORDER BY alphabet first then follow by number

Which uses this ORDER BY clause

ORDER BY IF(name RLIKE '^[a-z]', 1, 2), name

It will sort correctly

AAA

before

1AA

but puts

A1A

before

AAA

So it only seems to operate on the first character in the string.

score 0 · Answer 1 · answered Jun 24 '18 at 21:51

0

You can check for numbers instead of alphabets.

ORDER BY IF(name RLIKE '[0-9]', 2, 1), name

answered Jun 24 '18 at 21:51

Krish

5,917
2
14
35

The regex method fails on this pattern: it puts 11 before 1A. – gildedpage Jun 25 '18 at 05:03

score 0 · Accepted Answer · answered Jun 25 '18 at 05:03

Assuming all letters are upper case (as per your examples), use a case-sensitive (ie binary) order by after converting digits '0' through '9' to letters 'a' through 'j' respectively:

...
order by binary replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
  name, '0', 'a'), '1', 'b'), '2', 'c'), '3', 'd'), '4', 'e'), '5', 'f'), '6', 'g'), '7', 'h'), '8', 'i'), '9', 'j')

It's a bit clunky, but it works.

Mysql Sorting mixed strings of letters and numbers, letters before numbers

2 Answers2