Convert mysql query in magento query

Question

How to convert this query in magento style

    `SELECT MAX(s.num) FROM (
       SELECT CONVERT(SUBSTRING_INDEX(sku,'-',-1),UNSIGNED INTEGER) AS num 
       FROM catalog_product_entity 
       WHERE (sku REGEXP "^[0-9]+$")
    ) as s` 

If you offer a good article that will help me, then I will take it as an answer.

UPDATE

I write this query :

    $select = $connection->select()
                ->from(
                    $connection->select()->from(
                    'catalog_product_entity',
                    ['num' => new \Zend_Db_Expr('SELECT CONVERT(SUBSTRING_INDEX(sku,\'-\',-1),UNSIGNED INTEGER')]
                )->where(
                    $connection->quoteIdentifier('sku') . ' REGEXP :pattern'
                ),
                    [ new \Zend_Db_Expr('max(t.num)')]

        );
            print_r($select->__toString()); exit();

But select duplicated

SELECT max(t.num) FROM (
   SELECT SELECT CONVERT(SUBSTRING_INDEX(sku,'-',-1),UNSIGNED INTEGER AS `num` 
   FROM `catalog_product_entity` WHERE (`sku` REGEXP :pattern)
) AS `t`

Answer 1

Solution :

$select = $connection->select()
                ->from(
                    $connection->select()->from(
                    'catalog_product_entity',
                    ['num' => new \Zend_Db_Expr('CONVERT(SUBSTRING_INDEX(sku,\'-\',-1),UNSIGNED INTEGER)')]
                )->where(
                    $connection->quoteIdentifier('sku') . ' REGEXP :pattern'
                ),
                    [ new \Zend_Db_Expr('max(t.num)')]

        );

Answer 2

I simplified your select query which results the same output.

SELECT MAX(CONVERT(SUBSTRING_INDEX(sku,'-',-1),UNSIGNED INTEGER)) AS num 
FROM catalog_product_entity 
WHERE (sku REGEXP "^[0-9]+$")

Here is the equivalent magento code.

$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToFilter('sku', array("regexp" => "^[0-9]+$")); 
/* Filters catalog_product_entity based on sku matching the required regex pattern */

$collection->getSelect()
    ->reset(Zend_Db_Select::COLUMNS);


$collection->addExpressionAttributeToSelect('num',
        new Zend_Db_Expr('MAX(CONVERT(SUBSTRING_INDEX(sku,"-",-1),UNSIGNED INTEGER))'),
        []);/* Includes the required expression on sku column and results in the field num */


/* To cross check the query that executes behind this $collection,
you may echo $collection->getSelect()->assemble() which outputs the query that executes behind it. */

/* Final Result - num value */
$finalResult =  $collection->getFirstItem()->getNum();