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 ‘’,
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 ?
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?
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.
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
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 ?
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