SQL search multiple values in same field

Try this

Using UNION

$sql = '';
$count = 0;
foreach($search as $text)
{
  if($count > 0)
     $sql = $sql."UNION Select name From myTable WHERE Name LIKE '%$text%'";
  else
     $sql = $sql."Select name From myTable WHERE Name LIKE '%$text%'";

  $count++;
}

Using WHERE IN

$comma_separated = "('" . implode("','", $search) . "')";  // ('1','2','3')
$sql = "Select name From myTable WHERE name IN ".$comma_separated ;

This will works perfectly in both cases, one or multiple fields searching multiple words.

Hope this will help someone. Thanks

declare @searchTrm varchar(MAX)='one two three four'; 
--select value from STRING_SPLIT(@searchTrm, ' ') where trim(value)<>''
select * from Bols 
WHERE EXISTS (SELECT value  
    FROM STRING_SPLIT(@searchTrm, ' ')  
    WHERE 
        trim(value)<>''
        and(    
        BolNumber like '%'+ value+'%'
        or UserComment like '%'+ value+'%'
        or RequesterId like '%'+ value+'%' )
        )

Yes, you can use SQL IN operator to search multiple absolute values:

SELECT name FROM products WHERE name IN ( 'Value1', 'Value2', ... );

If you want to use LIKE you will need to use OR instead:

SELECT name FROM products WHERE name LIKE '%Value1' OR name LIKE '%Value2';

Using AND (as you tried) requires ALL conditions to be true, using OR requires at least one to be true.