Specific Order By in SQL to sort by letter then by number

Databases
1

Hello,
It makes some days I’m lookign to get the right order by to sort my result by letter then by number. I used this request which is correct for 80%.

ORDER BY
IF( DATA REGEXP ‘[1]’,
CONCAT(LEFT(LPAD(DATA, 1, ‘0’), 2),
LPAD(SUBSTRING(DATA, 2), 20, ‘0’)),
CONCAT(‘@’,LPAD(DATA, 20, ‘0’))),

LENGTH( DATA ),
DATA

result is as below

|OA1|
|OA2|
|OA4|
|OA5|
|OA6|
|OA7|
|OA8|
|OB1|
|OB2|
|OB3|
|OB4|
|OB5|
|OB6|
|OB7|
|OB8|
|OB9|
|OA10|
|OA11|
|OA12|
|OA13|

And I would like to get OA10 just after OA9

Any idea obout this trouble ?

  1. A-Z ↩︎

2

I dont… really understand the regexp?
Without knowing much about what your data looks like, sort by the first two letters, then by the CAST as an INT value of the remainder of the string?

3

Hi, In fact my datas start from A1 to finish with WD15…
I found this script on Internet but as I’m a newbee in dev I prefer to refer to real Dev to get a good order by.

4

There is no OA9 in your data sample. Nevertheless…
* I left out the pipes. Not sure what that is about but they probably don’t belong.

SELECT value
FROM test_table
ORDER BY
  SUBSTRING(value, 1, 2),  -- Sort by the first two characters (letters)
  CAST(SUBSTRING(value, 3) AS SIGNED);  -- Sort by the remaining characters (numbers) as integers
5

Hello Benanamen, At the origin I foudn you first sort for letter en digit.
Thanks for you quick answer and so happy to meet you here jaja.
I tried you sort but it doen’st work because some times I get only 1 Letter and not everytime 2.
I.E
My datas starts with A like A1 to A15 and finish to WA1-WA15 to WD1 → WD15
Do you undertsand to trick ?

6

This should work, but of course it is very bad in performance.

ORDER BY REGEXP_REPLACE(column, '[^a-zÀ-ÿ ]', ''), CAST(REGEXP_REPLACE(column, '[^0-9]', '') AS INT)

I would recommend two split the column into two columns. Otherwise your queries will become very slow when you have thousands of entries.

1 Like
8

Hello, So Finally I amended my datas to make the first script from benanamen ok.
I just add a “0” on all my datas concerned by the trouble.
It was like A1->A9 and I amended to A01->A09.
Now everything works well !
I would like to thank you for the help, I really appreciate !

9

I mean… technically if you add 0s to all your single digit numbers, and never have a number above 99, you can just… sort regularly.

EDIT: Though that would put AA immediately after A, and B would come after AZ.

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.