How can I get next/ previous post links to order by a filter (by the last word of the title)?

I am trying to use the next/ previous post link functions by WordPress to move between posts of a custom type based on the surname of a person.

The custom type represents a person with a post title in the format “Forename Surname”.

I have ordered the posts on an archive page by the surname but am struggling to work out how I could get the next/ previous links to also follow this filter. I have seen another similar question and have managed to order the next/ previous links by title but only alphabetically on the first name.

If anyone has any idea how this could be done, the help would be much appreciated.

Reviewing the MySQL string functions, it looks like you could use the SUBSTRING_INDEX() function:

ORDER BY SUBSTRING_INDEX( p.post_title, ' ', -1 ) {ASC|DESC} LIMIT 1

to order by the last word of the post title.

You could test this method within your orderby filters.

Example: Prev/Next CPT – ordered by last word in post title

This is an example how one could try to modify the linked answer by @WillLanni:

a) Next part for the custom post type cpt:

// Next CPT
add_filter( 'get_next_post_where', function( $where, $in_same_term, $excluded_terms )
{
    global $post, $wpdb;

    // Edit this custom post type to your needs
    $cpt="post";

    // Current post type
    $post_type = get_post_type( $post );

    // Nothing to do    
    if( $in_same_term || ! empty( $excluded_terms ) || $cpt !== $post_type )
        return $where;

    // Next CPT order by last word in title
    add_filter( 'get_next_post_sort', function( $orderby ) 
    {
        return " ORDER BY SUBSTRING_INDEX( p.post_title, ' ', -1 ) ASC LIMIT 1 ";
    } );

    // Modify Next WHERE part
    return $wpdb->prepare( 
        " WHERE 
            SUBSTRING_INDEX( p.post_title, ' ', -1 ) > SUBSTRING_INDEX( '%s', ' ', -1 ) 
            AND p.post_type="%s" 
            AND p.post_status="publish"
        ", 
        $post->post_title, 
        $post_type 
    );    

}, 10, 3 );

b) Previous part for the custom post type cpt:

// Previous CPT
add_filter( 'get_previous_post_where', function( $where, $in_same_term, $excluded_terms)
{
    global $post, $wpdb;

    // Edit this custom post type to your needs
    $cpt="post";

    // Current post type
    $post_type = get_post_type( $post );

    // Nothing to do    
    if( $in_same_term || ! empty( $excluded_terms ) || $cpt !== $post_type )
        return $where;

    // Previous CPT, order by last word in post title
    add_filter( 'get_previous_post_sort', function( $orderby )
    {
        return " ORDER BY SUBSTRING_INDEX( p.post_title, ' ', -1 ) DESC LIMIT 1 ";
    } );

    // Modify Prev WHERE part
    return $wpdb->prepare( 
        " WHERE 
            SUBSTRING_INDEX( p.post_title, ' ', -1 ) < SUBSTRING_INDEX( '%s', ' ', -1 ) 
            AND p.post_type="%s" 
            AND p.post_status="publish"
        ", 
        $post->post_title, 
        $post_type 
    );

}, 10, 3 );

where you modify the custom post type 'cpt' to your needs.