SELECT * FROM foobar
  WHERE userid != '100' AND col1 REGEXP '[[:<:]]test[[:>:]]'
     OR userid != '100' AND col2 REGEXP '[[:<:]]test[[:>:]]'
     OR userid != '100' AND col3 REGEXP '[[:<:]]test[[:>:]]' 

This question is working acceptable for me. It'll filter essentially on two criteria

  1. where col1 or col2 or col3 have "test" and
  2. userid isn't 100.

I've another col4 that we want that, apart from the above two condition, it has to remove individuals results where col4 = 'y'

How must i modify this above query?

You possess an error inside your query, You have to alter the second col2 to col3. It's simpler to determine the mistake should you reformat your code:

SELECT * FROM foobar
WHERE (userid != '100' AND col1 REGEXP '[[:<:]]test[[:>:]]') OR
      (userid != '100' AND col2 REGEXP '[[:<:]]test[[:>:]]') OR
      (userid != '100' AND col2 REGEXP '[[:<:]]test[[:>:]]')

I have also added parentheses to create the evaluation order obvious. You are able to rewrite your query to prevent repeating the expression userid != '100'. Then just add your missing clause:

SELECT * FROM foobar
WHERE userid != '100'
AND col4 <> 'y'
AND (
    col1 REGEXP '[[:<:]]test[[:>:]]' OR
    col2 REGEXP '[[:<:]]test[[:>:]]' OR
    col3 REGEXP '[[:<:]]test[[:>:]]'
)

Something similar to this? You might separate the userid != '100' as it is present with the 3 inspections.

SELECT * 
FROM foobar 
WHERE userid != '100' 
  AND (col1 REGEXP '[[:<:]]test[[:>:]]' 
       OR col2 REGEXP '[[:<:]]test[[:>:]]' 
       OR col3 REGEXP '[[:<:]]test[[:>:]]' ) 
  AND col4 != 'y'
SELECT * 
    FROM foobar 
    WHERE userid != '100' 
        AND (col1 REGEXP '[[:<:]]test[[:>:]]' 
          OR col2 REGEXP '[[:<:]]test[[:>:]]'
          OR col3 REGEXP '[[:<:]]test[[:>:]]')
        AND col4 <> 'y'

Do this:

SELECT * 
    FROM foobar 
 WHERE  col4 = 'y'
   OR   (
                userid != '100' 
                 AND (
                                col1 REGEXP '[[:<:]]test[[:>:]]' 
                                OR
                                col2 REGEXP '[[:<:]]test[[:>:]]' 
                                OR 
                                col2 REGEXP '[[:<:]]test[[:>:]]'
                            )
                )
SELECT * FROM foobar WHERE 
(userid != '100') AND 
(col1 REGEXP '[[:<:]]test[[:>:]]' OR userid != '100' OR col2 REGEXP '[[:<:]]test[[:>:]]' OR col2 REGEXP '[[:<:]]test[[:>:]]')
AND col4 <> 'y'