Wordpress - sort posts containing numbers and letters alphabetically

Question

I'm trying to sort the posts in WP alphabetically but I'm not getting the result I was expecting. This is what I get:

  NOZ - 1 apple 
  NOZ - 10 orange 
  NOZ - 11 banana
  NOZ - 2 tree 
  NOZ - 3 grass

This is what I'd like to get:

  NOZ - 1 apple 
  NOZ - 2 tree 
  NOZ - 3 grass 
  NOZ - 10 orange 
  NOZ - 11 banana

This is my PHP code:

<?php $loop = new WP_Query( array('post_type' => 'myPosts', 'orderby' => 'title', 'order' => 'ASC', 'posts_per_page' => -1 )    ); ?>

Thanks a lot for your help.

score 8 · Accepted Answer · answered Jul 01 '17 at 05:50

You can add a MySQL function to the orderby field. For example:

'orderby' => 'CAST(SUBSTRING(title, 7) as unsigned)'

The substring function extracts all characters after and including the character at the 7th position.

The cast function converts the extracted characters to an unsigned integer. The cast function works from left to right. It discards all characters that are not numeric. A similar question was answered here: SQL order string as number

Wordpress - sort posts containing numbers and letters alphabetically

1 Answers1