Sort the data in alphanumeric order using postgresql

Question

I am using php 5.3 for my code. and i want to sort my data in following format.

Building 01 - 101
Building 01 - 150

Building 02 - 100
Building 02 - 105
Building 03 - 099

public static function fetchSortedPropertyUnits() {
    $strSql = 'SELECT pu.*,pb.building_name
               FROM property_units pu  
                LEFT JOIN property_buildings pb ON( pu.property_building_id = pb.id )  
                WHERE pu.management_company_id = ' . $intManagementCompanyId . '
                    AND pu.property_id = ' . $intPropertyId . '  
               ORDER BY  
                COALESCE ( CAST ( SUBSTRING ( pb.building_name FROM \'([a-zA-Z ]{1,26})\' ) AS VARCHAR ), \'\' ),
                    COALESCE ( CAST ( SUBSTRING ( pb.building_name FROM \'([0-9]{1,10})\' ) AS INTEGER ), 0 ),
                    COALESCE ( CAST ( SUBSTRING ( pu.unit_number FROM \'([a-zA-Z ]{1,26})\' ) AS VARCHAR ), \'\' ),
                    COALESCE ( CAST ( SUBSTRING ( pu.unit_number FROM \'([0-9]{1,10})\' ) AS INTEGER ), 0 ),
                    pb.building_name,
                    pu.unit_number';  
            return self::fetchPropertyUnits( $strSql, $objDatabase );  }

This is the fetch function i used.
& i use it in my code as follows.

$arrobjSortedPropertyUnits  =   CPropertyUnits::fetchSortedPropertyUnits( $this->m_objPropertyUtilitySetting->getManagementCompanyId(), $this->m_objPropertyUtilitySetting->getPropertyId(), $this->m_objClientDatabase );  
foreach( $this->m_arrobjPropertyUnits as $objPropertyUnit ) {  
    $strUnitNumber = $objPropertyUnit->getUnitNumber();  
    if( true == valObj( $objPropertyUnit, 'CPropertyUnit' ) && true == $objPropertyUnit->getPropertyBuildingId() ) {  
        $strUnitNumber = $objPropertyUnit->getBuildingName() . ' - ' . $objPropertyUnit->getUnitNumber();  
        $objPropertyUnit->setUnitNumber( $strUnitNumber );  
    }  
}

I want to sort it in proper order, if property don't have building then only sort it by unit numbers. Any help is welcome for this issue. Thanks.

I'm confused - the building name `Building 01 - 101` is a string, if you are sorting by the first number then the second, and they are padded, wouldn't a lexicographic sort do? — Elle, May 17 '13 at 05:33
Those *do* sort in the natural ordering. Please show the *real* underlying data you're working from. Try http://sqlfiddle.com/ . You also forgot to include your PostgreSQL version. — Craig Ringer, May 17 '13 at 05:34
You might find this question useful too: http://stackoverflow.com/questions/12965463/humanized-or-natural-number-sorting-of-mixed-word-and-number-strings — Craig Ringer, May 17 '13 at 05:34
Here the Building number is string and 101 i.e. unit number is also varchar type. — Pradeep, May 17 '13 at 05:36
Don't know if that's beyond your project's scope, but have you considered using an object relational mapping framework like [Propel](http://propelorm.org) or [Doctrine](http://www.doctrine-project.org) to simplify issues of this kind? Am I right to assume that building numbers and unit numbers are stored in different columns, both as VARCHARs, and you would like to output the selection ordered by both of them? If so, this would simply be something like ORDER BY pb.building_name ASC, pu.unit_number ASC. If one of those doesn't have values the result set would still be sorted by the other one. — twigmac, May 17 '13 at 16:33

score 0 · Answer 1 · answered Jun 07 '13 at 07:36

In this case you need to look at your strings and see how to process them. it looks like have a string in the form of "Building X - Y" and you want to sort on X then Y. The simple thing to do is to turn this into a numeric array. You can do this by:

 .....
 ORDER BY string_to_array(regexp_replace(building_name, 'Building ', ''), ' - ')::int[]

This will turn "Building X - Y" into {X,Y} so Building 1 - 100 becomes {1,100} and so forth. These will be ordered starting with the left-most element.

Sort the data in alphanumeric order using postgresql

1 Answers1