Query posts that not start with a letter (a-z) but everyting else

  • Resolved peesen87

    (@peesen87)


    2 years, 7 months ago

    Hello everyone

    With the following function i can easily query all posts that start with a certain letter:

    function wpse_298888_posts_where( $where, $query ) {
    global $wpdb;

    $starts_with = esc_sql( $query->get( 'starts_with' ) );

    if ( $starts_with ) {
        $where .= " AND $wpdb->posts.post_title LIKE '$starts_with%'";
    }

    return $where;
}
add_filter( 'posts_where', 'wpse_298888_posts_where', 10, 2 );

    But now i need to query all posts that are not starting with a default letter from a to z. Is this possible? Thank you so much!

Viewing 4 replies - 1 through 4 (of 4 total)
  • Moderator bcworkz

    (@bcworkz)

    2 years, 7 months ago

    Use the SQL REGEXP operator. Something like
    " AND $wpdb->posts.post_title REGEXP '^[^A-Za-z]'"
    (untested)
    https://www.mysqltutorial.org/mysql-regular-expression-regexp.aspx

    Thread Starter peesen87

    (@peesen87)

    2 years, 7 months ago

    @bcworkz Hey, i created a title filter but its not workign as it should. Still all posts are being queried: 

    function title_filter( $where, $query ) {
    global $wpdb;
    
    $search_artist_title = esc_sql( $query->get( 'search_artist_title' ) );
    
    if ( $search_artist_title ) {
        $where .= " AND $wpdb->posts.post_title NOT REGEXP '$search_artist_title%'";
    }
    return $where;
}
add_filter( 'posts_where', 'title_filter', 10, 2 );

    Do you have an idea what the problem could be?

    Moderator bcworkz

    (@bcworkz)

    2 years, 7 months ago

    What is the value of $search_artist_title? I doubt it’s a valid regexp, as that would be an unusual plain text search term. Also, % isn’t a wildcard in regexp. The equivalent would be .*. But it may not even be necessary if your goal is to not match the first character. Regexp can do that without .*, the subsequent string after the match is an implicit wildcard match.

    If you need help constructing a proper regexp, try https://regexr.com. It’s kind of a regexp fiddle site.

    Thread Starter peesen87

    (@peesen87)

    2 years, 7 months ago

    @bcworkz Its working now :).

    function title_filter( $where, $query ) {
    global $wpdb;
    
    $search_artist_title = esc_sql( $query->get( 'search_artist_title' ) );
    
    if ( $search_artist_title ) {
        $where .= " AND $wpdb->posts.post_title NOT REGEXP '$search_artist_title'";
    }
    return $where;
}
add_filter( 'posts_where', 'title_filter', 10, 2 )

    And the regex:

    $args = array(
            'posts_per_page'=> -1,
            'search_artist_title' => '^[A-Za-zäöüÄÖÜ]',
 );

    Thank you very much for your help!

    • This reply was modified 2 years, 7 months ago by peesen87.
Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Query posts that not start with a letter (a-z) but everyting else’ is closed to new replies.