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
According to the screen room A101 must come after room 101 and room B102 must come after room 102 etc.
i tried the following
Room::where(.....) ->orderByRaw("CAST(room_number as UNSIGNED) ASC") ->get();
also i tried to sort the collecting after getting it
Room::where(.....) ->orderByRaw("CAST(room_number as UNSIGNED) ASC") ->get() ->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
Room::where(.....) ->orderByRaw("REGEXP_REPLACE(room_number, '[^0-9]+', '') ASC") ->get();
Room::where(.....) ->orderByRaw("REGEXP_SUBSTR(room_number,'[0-9]+') ASC") ->get();
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