MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tb_name (
 col_name varchar(30)
)
insert into tb_name values 
('John'), 
('Foo'),
('Jane')

Records: 3  Duplicates: 0  Warnings: 0

select *
from tb_name 
where 'John Foo' like concat('%',col_name,'%') 

col_name

John

Foo

select *
from tb_name 
where col_name IN ('John','Foo')

col_name

John

Foo

select t.*
from tb_name t
cross join (select 'John Foo Bar' name) names
where t.col_name IN (
           substring_index(name,' ',1), 
           substring_index(substring_index(name,' ', 2),' ',-1), 
           substring_index(substring_index(name,' ', 3),' ',-1)
          )

col_name

John

Foo