Order posts ascending with number in title

Old answer (which no longer works):

A sneaky way to do it using MySQL, assuming the number’s always at the end, is to reverse the title, convert it to a number (+0), and then reverse again and convert it to a number:

function wpse174646_posts_orderby( $orderby, $query ) {
    if ( $query->get( 'orderby' ) != 'title_number' ) return $orderby;
    global $wpdb;
    return 'REVERSE(REVERSE(TRIM(' . $wpdb->posts . '.post_title))+0)+0 ' . $query->get( 'order' );
}
$args = array( 'orderby' => 'title_number', 'order' => 'ASC' );
add_filter( 'posts_orderby', 'wpse174646_posts_orderby', 10, 2 );
$query = new WP_Query( $args );
remove_filter( 'posts_orderby', 'wpse174646_posts_orderby', 10 );

New answer:

A robust (hopefully) way to do it is to use the answer I posted on stackexchange (https://stackoverflow.com/a/28808798/664741), a simplified non-udf version of the best response there of @plaix/Richard Toth/Luke Hoggett, which pads out the (first encountered) number with zeros:

add_filter( 'posts_clauses', function ( $pieces, $query ) {
    if ( $query->get( 'orderby' ) != 'title_number' ) return $pieces;
    global $wpdb;
    $field = $wpdb->posts . '.post_title';
    $pieces[ 'fields' ] .= $wpdb->prepare(
        ', LEAST(' . implode( ',', array_fill( 0, 10, 'IFNULL(NULLIF(LOCATE(%s, ' . $field . '), 0), ~0)' ) )
        . ') AS first_int',
        '0', '1', '2', '3', '4', '5', '6', '7', '8', '9'
    );
    $pieces[ 'orderby' ] = $wpdb->prepare(
        'IF(first_int = ~0, ' . $field . ', CONCAT('
            . 'SUBSTR(' . $field . ', 1, first_int - 1),'
            . 'LPAD(CAST(SUBSTR(' . $field . ', first_int) AS UNSIGNED), LENGTH(~0), %s),'
            . 'SUBSTR(' . $field . ', first_int + LENGTH(CAST(SUBSTR(' . $field . ', first_int) AS UNSIGNED)))'
        . ')) ' . $query->get( 'order' )
        , 0
    );
    return $pieces;
}, 10, 2 );
$args = array( 'orderby' => 'title_number', 'order' => 'ASC' );
$query = new WP_Query( $args );