with names(first_name,middle_name,last_name)
    as(select 'John','','Doe' from dual
    union
    select 'Jane','Mary','Doe' from dual
    union
    select 'John','','Smith' from dual
    union
    select 'George','Smith','Michael' from dual
    union
    select 'John','Lee','Smith' from dual
    )
    select *
    from names
    where  lower(first_name||middle_name||last_name) like '%john%smith%'

FIRST_ MIDDL LAST_NA
------ ----- -------
John   Lee   Smith
John         Smith