Ordering By a Varchar column is that even possible?-CodePudding

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 !

CodePudding user response：

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 CASE WHEN"