Skip to content
Advertisement

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   |
------------------

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?

Advertisement

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

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 
8 People found this is helpful
Advertisement