Adds FIND_IN_SET support as a comparison operator for WordPress meta_query. Include this in a plugin, in the theme's functions.php file, or as a separate file that is loaded using require() or include() before your call to WP_Query. As @Otto42 points out, you might not want to use this in a plugin that you plan to distribute given the WordPress'…

find-in-set.php

<?php
/**
 * Class Add_Find_In_Set_Compare_To_Meta_Query
 */
class Add_Find_In_Set_Compare_To_Meta_Query {

  /**
   *
   */
  function __construct() {
    add_action( 'posts_where', array( $this, 'posts_where' ), 10, 2 );
  }

  /**
   * Adds value 'find_in_set' to meta query 'compare' var for WP_Query 
   *
   * query['meta_query'][{n}]['compare'] => 'find_in_set'
   * @example This will find in set where _related_post_ids is a string of comma separated post IDs.
   *
   *    $query = new WP_Query( array(
   *      'posts_per_page' => -1,
   *      'post_type' => 'post'
   *      'meta_query' => array(
   *        array(
   *          'key' => '_related_post_ids',
   *          'value' => $related_post->ID,
   *          'compare' => 'find_in_set',
   *        )
   *      ),
   *    );
   *
   * @param array $where
   * @param object $query
   *
   * @return array
   */
  function posts_where( $where, $query ) {
    global $wpdb;
    foreach( $query->meta_query->queries as $index => $meta_query ) {
      if ( isset( $meta_query['compare'] ) && 'find_in_set' == strtolower( $meta_query['compare'] ) ) {
        $regex = "#\(({$wpdb->postmeta}.meta_key = '" . preg_quote( $meta_query['key'] ) . "')" .
        " AND (CAST\({$wpdb->postmeta}.meta_value AS CHAR\)) = ('" . preg_quote( $meta_query['value'] ) . "')\)#";
        /**
         * Replace the compare '=' with compare 'find_in_set'
         */
        $where = preg_replace( $regex, "($1 AND FIND_IN_SET($3,$2))", $where );
      }
    }
    return $where;
  }

}
new Add_Find_In_Set_Compare_To_Meta_Query();

I want to use this in my complex search query where one postmeta field value is comma separated (1,2,3,4...) and I want to get the post result by comparing min and max value of this field , can you please assist me ?

Exactly what I was looking for. Thanks!

This isn't working I have values in comma separated like 1,2,3 but when Use find in set it is not replacing = with find_in_set format

This isn't working I have values in comma separated like 1,2,3 but when Use find in set it is not replacing = with find_in_set format

Can you post a snippet of code you are using so I can see what the issue is? Also, can you post the SQL query found in your WP_Query->request?

This isn't working I have values in comma separated like 1,2,3 but when Use find in set it is not replacing = with find_in_set format

Can you post a snippet of code you are using so I can see what the issue is? Also, can you post the SQL query found in your WP_Query->request?

Here are the request params
https://prnt.sc/u2lsdo

I have echo your statement in find in set file
https://prnt.sc/u2lssw

What query result I am getting
https://prnt.sc/u2lt24

If anyone is having issues with this, I recently needed something like this and noticed on my Wordpress install, I wasn't getting that CAST variable in my WHERE clause. I updated the regex to look for what Wordpress was giving me. Maybe this will help someone else out.

add_action('posts_where', [$this, 'postsWhereFindInSet'], 10, 2);

public function postsWhereFindInSet($where, $query)
{
    global $wpdb;

    foreach ($query->meta_query->queries as $index => $meta_query) {
        if (isset($meta_query['compare']) && 'find_in_set' == strtolower($meta_query['compare'])) {
            $postMetaTable = $wpdb->postmeta;
            $metaKey = $meta_query['key'];
            $metaValue = $meta_query['value'];

            $regex = "#\\([\n\r\\s]+({$postMetaTable}.meta_key = \\'" . preg_quote($metaKey) . "\\') AND ({$postMetaTable}.meta_value) = (\\'" . preg_quote($metaValue) . "\\')[\n\r\\s]+\\)#m";

            /**
             * Replace the compare '=' with compare 'find_in_set'.
             */
            $where = preg_replace($regex, '($1 AND FIND_IN_SET($3,$2))', $where);
        }
    }

    return $where;
}

Thanks to Mike, for the initial code on this!

@aolin480 — Thanks for posting.

@providernexus — Sorry, I never got a notice that you posted. If the answer @aolin480 doesn't work for you feel free to email me: mike at newclarity dot net.

Thanks for the code.
A small update on the note that this works only if single meta is requested or if find_in_set is the first meta key.

This is because only first JOIN is done as {$wpdb->postmeta}.meta_key
All other joins are eg: INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) (mt2, mt3, etc...)
which then results in different SQL and it's not replaced.

I have refactored code a little bit to make it more readable for me and added a tweak to support any position of meta.
Have tested only with one for now, I assume it could work for multiple too.

foreach ($query->meta_query->queries as $i => $mq)
{
    if (isset($mq['compare']) && 'find_in_set' == strtolower($mq['compare']))
    {
        /**
         * Fix for other JOINS
         * All keys after first are joined as 
         * INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
         */
        $prefix = ( 0 == $i )
            ? $wpdb->postmeta
            : 'mt' . $i;

        $regex = sprintf(
            "#\\([\n\r\\s]+(%s.meta_key = '%s') AND (%s.meta_value) = ('%s')[\n\r\\s]+\\)#m",
            $prefix,
            preg_quote($mq['key']),
            $prefix,
            preg_quote($mq['value'])
        );

        // Replace the compare '=' with compare 'find_in_set'.
        $where = preg_replace($regex, '($1 AND FIND_IN_SET($3,$2))', $where);
    }
}