SQL globally - and Oracle too is very poor at string handling...
In your case you have to find a way to split the name around white-space characters and then compare to see if there are repeated parts...
(Consider that not all names are made of two parts only - I have 3 of them for instance)
The code you have there has nothing to do with your requirement - if finds duplicate values across records not inside a single field...
So, if you can not skip this requirement - do it in code not in SQL (it is next to impossible to create a all-purpose compare method nad when you done, you will find it very poorly perform)...
-- EDIT
Here some SQL (not Oracle tested) that can split a string and create a table where every part is a row... You may create an call a function based on it and test it for duplicates using standard SQL... But beware - it has very bad performance...
DECLARE @NAME AS NVARCHAR(MAX) = 'jhon jhon terry perry'
DECLARE @NAME_PART TABLE
(
PART NVARCHAR(MAX)
)
DECLARE @CURVAL AS NVARCHAR(MAX)
DECLARE @ALLVALS AS NVARCHAR(MAX)
SET @ALLVALS = @NAME + ' '
WHILE LEN (@ALLVALS) > 0
BEGIN
SET @CURVAL = LEFT(@ALLVALS, CHARINDEX(' ', @ALLVALS) - 1)
INSERT INTO @NAME_PART SELECT @CURVAL
SET @ALLVALS = RIGHT(@ALLVALS, LEN(@ALLVALS) - CHARINDEX(' ', @ALLVALS) + 1)
END
SELECT * FROM @NAME_PART