Ordering By a Varchar column is that even possible?


am using Laravel for some sass project where some deals are one step behind completion if just our rooms accepts ( alphabetic ) beside the actual numbers for example
101 – A101 ect.

So room_number column data type was int so i converted it to a varchar no problem until now and everything is working perfect .

Received a new request that we want to sort those rooms in a specific way so for example if i have rooms 101, 102,A101,103,B102 they must be sorted asc as 101,A101,102,B102,103

The Below Screen might give you something of what am trying to achieve

enter image description here

According to the screen room A101 must come after room 101 and room B102 must come after room 102 etc.

i tried the following

->orderByRaw("CAST(room_number as UNSIGNED) ASC")

also i tried to sort the collecting after getting it

    ->orderByRaw("CAST(room_number as UNSIGNED) ASC")
    ->sortBy('order', SORT_REGULAR, true); 

But all my tries came to a dead road !
am thing to perform a binary search on those rooms and apply a custom solution but i thought i could ask first maybe someone had the same issue !


You can use either REGEXP_REPLACE() or REGEXP_SUBSTR() to extract numeric value for sort, here is a sample of how to do it

    ->orderByRaw("REGEXP_REPLACE(room_number, '[^0-9]+', '') ASC")


    ->orderByRaw("REGEXP_SUBSTR(room_number,'[0-9]+') ASC")

Note: If you need more customization consider use "ORDER BY

Answered By – mwafi

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published