How can I use ORDER BY column_string like column_int when my column_string have letters?

I have a table like:

------------------
| ID  |  Animal  |
------------------
|  1  |    B1    |
------------------
|  2  |    B2    |
------------------
|  3  |    B3    |
------------------
|  4  |    B4    |
------------------
|  5  |    B5    |
------------------
|  6  |     1    |
------------------
|  7  |     2    |
------------------
|  8  |    R71   |
------------------
|  9  |    R72   |
------------------
|  10  |    4    |
------------------
| 11   |    7    |
------------------
| 12   |    R1   |
------------------
| 13   |    77   |
------------------
| 14   |    3    |
------------------
| 15   |    5    |
------------------
| 16   |    R2   |
------------------
| 17   |    T3   |
------------------
| 18   |    C50  |
------------------
| 19   |    R5   |
------------------
| 20   |    R6   |
------------------

​x
 
------------------| ID  |  Animal  |------------------|  1  |    B1    |------------------|  2  |    B2    |------------------|  3  |    B3    |------------------|  4  |    B4    |------------------|  5  |    B5    |------------------|  6  |     1    |------------------|  7  |     2    |------------------|  8  |    R71   |------------------|  9  |    R72   |------------------|  10  |    4    |------------------| 11   |    7    |------------------| 12   |    R1   |------------------| 13   |    77   |------------------| 14   |    3    |------------------| 15   |    5    |------------------| 16   |    R2   |------------------| 17   |    T3   |------------------| 18   |    C50  |------------------| 19   |    R5   |------------------| 20   |    R6   |------------------​

My DB is shared hosted where I don’t have any possibility to set new configurations and, until last week, my query with final ORDER BY animal + 0 ASC was returning the result orderly, like: B1…B5, R1…R72, 1…700. Yesterday our database returned all results, with the same query, disorderly while, in developer DB, it’s returned orderly as always.

I consult the host service, but it affirms there’s no problem with DB and it doesn’t have new configuration.

I tried to use CAST() and CONVERT() function, but it returned animal disorderly.

Can someone shed light on this issue?

Answer

For this sample data this will work:

SELECT * 
FROM tablename
ORDER BY Animal + 0, 
         CASE WHEN Animal + 0 = 0 THEN LEFT(Animal, 1) END,
         CASE WHEN Animal + 0 = 0 THEN SUBSTR(Animal, 2) + 0 END

 
SELECT * FROM tablenameORDER BY Animal + 0,          CASE WHEN Animal + 0 = 0 THEN LEFT(Animal, 1) END,         CASE WHEN Animal + 0 = 0 THEN SUBSTR(Animal, 2) + 0 END​

See the demo.
Results:

> ID | Animal
> -: | :-----
>  1 | B1    
>  2 | B2    
>  3 | B3    
>  4 | B4    
>  5 | B5    
> 18 | C50   
> 12 | R1    
> 16 | R2    
> 19 | R5    
> 20 | R6    
>  8 | R71   
>  9 | R72   
> 17 | T3    
>  6 | 1     
>  7 | 2     
> 14 | 3     
> 10 | 4     
> 15 | 5     
> 11 | 7     
> 13 | 77

 
> ID | Animal> -: | :----->  1 | B1    >  2 | B2    >  3 | B3    >  4 | B4    >  5 | B5    > 18 | C50   > 12 | R1    > 16 | R2    > 19 | R5    > 20 | R6    >  8 | R71   >  9 | R72   > 17 | T3    >  6 | 1     >  7 | 2     > 14 | 3     > 10 | 4     > 15 | 5     > 11 | 7     > 13 | 77 ​

Advertisement

Answer