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();

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);
    }
}