MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (
  `name` VARCHAR(100)
) ;

INSERT INTO users
  (`name`)
VALUES
  ('John Doe'),
  ('John Spelling'),
  ('Rob Lowe'),
  ('Rob Williams'),
  ('test1'),
  ('other test')
  ;
  
SELECT * FROM users;
  


name


John Doe

John Spelling

Rob Lowe

Rob Williams

test1

other test


SELECT
  DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ' ', n.digit+1), ' ', -1) val
FROM
  ( SELECT 'Rob Doe' as vals) as tt1
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(vals, ' ' , '')) <= LENGTH(vals)-n.digit;


val


Rob

Doe


With split_parameter as (
    SELECT
      DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ' ', n.digit+1), ' ', -1) val
    FROM
      ( SELECT 'Rob Doe' as vals) as tt1
      INNER JOIN
      (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
      ON LENGTH(REPLACE(vals, ' ' , '')) <= LENGTH(vals)-n.digit
)
SELECT name, CONCAT('%', val, '%') as filter
FROM users
JOIN split_parameter
  ON users.name like CONCAT('%', val, '%')


name
filter


Rob Lowe
%Rob%

Rob Williams
%Rob%

John Doe
%Doe%