MySQL OrderBY with string, number and null values-CodePudding

I have a problem with sorting data in MySQL.

I have a varchar column, and in that column random texts are being saved, and I can't find a way to order correctly.

I have the following data:

| areas   | 
| ------- | 
| 1       | 
| 3       | 
| null    |
| area 01 |
| area 02 |
| 36548   |
| null    |
| area 20 |
| area 1  |
| area 4  |

I tried the following solution in OrderBy:

OrderBy
IF(column REGEXP '^[A-Z]', 
  CONCAT(LEFT(column, 1), 
  LPAD(SUBSTRING(column, 3), 20, '0')
), 
CONCAT('@', LPAD(column, 20, '0'))), 
LENGTH( column ), column ASC #Or DESC

But the result is wrong:

| areas   | 
| ------- | 
| null    | 
| null    |
| 36548   | 
| area 01 |
| area 02 |
| area 1  |
| area 20 | #Problem
| area 4  |

If I change the order to DESC it keeps the null data first and not last.

Request: I would like to leave it in the correct order, respecting the alphanumeric order, regardless of the data entered.

CodePudding user response：

Maybe with some logic applied in order by?

order by
  case 
    when areas is null then 'zzzz' 
    /* OTHER CONDITIONS */
    else areas
  end

CodePudding user response：

if you need that the null appears at the last, try this in mysql: ORDER BY COALESCE(areas, 999999);

SELECT areas
FROM Areas_table
ORDER BY COALESCE(areas, 999999);