Mysql AA should be greater than B in query-CodePudding

I've stored a X, Y coordinate system in mysql where x-axis values are from A, B....AA, AB... and so forth, just like columns are named in Excel. Y-axis values are from 1 to 100.

I can't figure out how to select all x-axis values that are "lower" than i.e. 'AZ'. My query result should be A, B, C...AA, AB...AZ values, but mysql considers B to be greater than AA.

SELECT x_axis, y_axis
FROM coordinates
WHERE x_axis<'AZ'
ORDER BY length(substring_index(x_axis, ' ', 1)), 
substring_index(x_axis, ' ', 1);

Hope my question makes sense.

Thanks

I managed to make the sorting correct, but I am stuck with the WHERE part and mysqls alphabetic sorting

CodePudding user response：

Your query should be ordered by two values: length of x_axis and x_axis values.

SELECT x_axis, y_axis
FROM coordinates
WHERE LPAD(x_axis, 2, ' ') < LPAD('AZ', 2, ' ')
ORDER BY LENGTH(x_axis), x_axis;

You could try sample query here: https://onecompiler.com/mysql/3yupevx2v

CodePudding user response：

String comparison in MySQL is case-sensitive, and it compares the ASCII values of the characters. Since 'Z' has a greater ASCII value than 'A', 'AZ' is considered greater than 'AA'.

To solve this issue, you can use the ORDER BY clause with a custom sorting order. You can use the FIELD() function to assign a custom order to your x_axis values, then use that value in the ORDER BY clause.

SELECT x_axis, y_axis FROM coordinates 
WHERE FIELD(x_axis, 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC'... )< 
FIELD('AZ') 
ORDER BY FIELD(x_axis, 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC'...);

If you want the solution to be more reusable you can set a user-defined variable to store the order of each x_axis value, and then use that variable in the ORDER BY clause.

SET @i:=0;
SELECT x_axis, y_axis, @i:=@i 1 as sort_order FROM coordinates 
WHERE x_axis<'AZ' 
ORDER BY sort_order;