Sort products by alphabets first and numbers last on listing page

Question

By default Magento has sorting by Name ASC OR DESC.

In my case, I have products names starting with numbers and Alphabets like Below and currently my product listing page lists products in this order.

.8" stand for all
07" to 09" stand for all
Madame First
Alexander First

I Need my output on the Product listings page to be like below.

Alexander First
Madame First
.8" stand for all
07" to 09" stand for all

After searching for a way in MYSQL, I have found that we need to use REGEX in ORDER BY.

I don't know how to use this in Magento 1.8.1. Can Any one suggest a good solution please?

Answer 1

File: app\code\local\Amasty\Sorting\Block\Catalog\Product\List\Toolbar.php (extends Mage_Catalog_Block_Product_List_Toolbar)

Method: setCollection

    parent::setCollection($collection);   

    if ($this->getCurrentOrder() != 'name'){
        continue;
    }


    $select = $collection->getSelect();

    $field = new Zend_Db_Expr("name REGEXP '^[a-zA-Z]' DESC");
    $select->order($field);

    // move to the first position
    $orders = $select->getPart(Zend_Db_Select::ORDER);
    if (count($orders) > 1){
        $last = array_pop($orders);
        array_unshift($orders, $last);
        $select->setPart(Zend_Db_Select::ORDER, $orders); 
    }