Using Orderby and meta_value to order natural/alphanumerical

The orderby argument is limited by what is possible in MySQL, and it’s not good at natural alphanumeric sorting like you describe. Possible options include:

• Rewriting the values with leading zeros, e.g. “CA05”
• Creating a separate “Sort Order” column that just stores an integer, and ordering by that.

• Separating the letters and numbers into two separate fields. Then writing a more complex query to sort by two different meta values:

  $query->set( 'meta_query', array(
      'relation' => 'AND',
      'letter_clause' => array(
          'key' => 'letter',
      ),
      'number_clause' => array(
          'key' => 'number',
          'type' => 'NUMERIC'
      )
  );
  $query->set( 'orderby', array(
      'letter_clause' => 'ASC',
      'number_clause' => 'ASC'
  );
  

  More info on that syntax here: https://www.billerickson.net/wp-query-sort-by-meta/