040, 044P, 041BL, 041W, 041PB
^^ This is the order it is coming out in by using Order By clause.
I think this is how it should appear instead:
040, 041BL, 041PB, 041W, 044P
I know similar questions must have been asked before, but I still can't figure out anything!
Edit: After X.L.Ant's comment, I realized my mistake. Therefore, simple order by clause is working for the test case given above. However, the case is still complicated if the number of digits is not always going to be 3 as GolezTrol mentioned. What should one do in that case?
Try:
SELECT string,
@num := CONVERT(string, signed) AS num_part,
Substring(Trim(LEADING '0' FROM string), Length(@num) + 1) AS rest_of_string
FROM table1
ORDER BY num_part,
rest_of_string
This way, the numbers will still be ordered by their numerical value (the leading 0s not being taken into account).
See fiddle.
try LPAD() function - but remeber that LPAD trims digits.