SQL> WITH test
  2          AS (SELECT 'green,blue,yellow, purple,green,yellow,purple,George,William,green,purple,blue,William'
  3                        col
  4                FROM DUAL),
  5       pieces AS (    SELECT TRIM (REGEXP_SUBSTR (col,
  6                                                  '[^,]+',
  7                                                  1,
  8                                                  LEVEL))
  9                                just_one
 10                        FROM test
 11                  CONNECT BY REGEXP_SUBSTR (col,
 12                                            '[^,]+',
 13                                            1,
 14                                            LEVEL)
 15                                IS NOT NULL)
 16    SELECT just_one, COUNT (*) cnt
 17      FROM pieces
 18  GROUP BY just_One
 19    HAVING COUNT (*) > 1;

JUST_ONE                    CNT
-------------------- ----------
green                         3
William                       2
blue                          2
yellow                        2
purple                        3

SQL>

SQL> col just_one format a15
SQL> WITH
  2    test AS (
  3      SELECT 'green,blue,yellow, purple,green,yellow,purple,George,William,green,purple,blue,William'
  4                 col
  5        FROM DUAL
  6    ),
  7    data as ( select ','||col||',' col from test ),
  8    pieces as (
  9      select trim(substr(col,
 10                         instr(col, ',', 1, level) + 1,
 11                         instr(col, ',', 1, level+1)
 12                         - instr(col, ',', 1, level) - 1)) just_one
 13      from data
 14      connect by level <= length(col)-length(replace(col,',',''))
 15    )
 16  SELECT just_one, COUNT (*) cnt
 17  FROM pieces
 18  GROUP BY just_One
 19  HAVING COUNT (*) > 1
 20  /
JUST_ONE               CNT
--------------- ----------
green                    3
William                  2
blue                     2
yellow                   2
purple                   3

5 rows selected.

select REGxp
from (
SELECT ExtractValue(column_value, '/element') REGxp
FROM   TABLE
(XMLSEQUENCE
(XMLTYPE
('<list><element>'
|| replace ('GREEN,BLUE,YELLOW,PURPLE,GREEN,YELLOW,PURPLE,GEORGE,WILLIAM,GREEN,PURPLE,BLUE,WILLIAM', 
',', '</element><element>')
|| '</element></list>').extract ('/list/*')))
) group by REGxp
having count(1)>1;

with t as (
           select 'GREEN,BLUE,YELLOW,PURPLE,GREEN,YELLOW,PURPLE,GEORGE,WILLIAM,GREEN,PURPLE,BLUE,WILLIAM' words from dual
          )
select  word
  from  t,
        xmltable(
                 '
                  for $word in distinct-values(ora:tokenize($words,","))
                  where ora:matches($words,fn:concat(",",$word,",(.*,)?",$word,","))
                   return $word
                 '
                 passing ',' || words || ',' as "words"
                 columns
                   word varchar2(30) path '.'
                )
/

WORD
------------------------------
GREEN
PURPLE
BLUE
YELLOW
WILLIAM

SQL> set timing on
SQL> begin
  2  for i in 1..10000 loop
  3  for rec in (select REGxp
  4  from (
  5  SELECT ExtractValue(column_value, '/element') REGxp
  6  FROM   TABLE
  7  (XMLSEQUENCE
  8  (XMLTYPE
  9  ('<list><element>'
 10  || replace ('GREEN,BLUE,YELLOW,PURPLE,GREEN,YELLOW,PURPLE,GEORGE,WILLIAM,GREEN,PURPLE,BLUE,WILLIAM',
 11  ',', '</element><element>')
 12  || '</element></list>').extract ('/list/*')))
 13  ) group by REGxp
 14  having count(1)>1) loop
 15  null;
 16  end loop;
 17  end loop;
 18  end;
 19  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:36.38
SQL> begin
  2  for i in 1..10000 loop
  3  for rec in (with t as (
  4             select 'GREEN,BLUE,YELLOW,PURPLE,GREEN,YELLOW,PURPLE,GEORGE,WILLIAM,GREEN,PURPLE,BLUE,WILLIAM' words from dual
  5            )
  6  select  word
  7    from  t,
  8          xmltable(
  9                   '
 10                    for $word in distinct-values(ora:tokenize($words,","))
 11                    where ora:matches($words,fn:concat(",",$word,",(.*,)?",$word,","))
 12                     return $word
 13                   '
 14                   passing ',' || words || ',' as "words"
 15                   columns
 16                     word varchar2(30) path '.'
 17                  )
 18  ) loop
 19  null;
 20  end loop;
 21  end loop;
 22  end;
 23  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.54
SQL>

CREATE OR REPLACE 
PACKAGE DUP_COL IS

TYPE DUP_COL_REC IS RECORD (DUP_COL VARCHAR2(2000));
TYPE DUP_COL_TBL IS TABLE OF DUP_COL_REC;

FUNCTION PIPE_DUP_COL(STR_COL IN VARCHAR2) RETURN DUP_COL_TBL PIPELINED;
END DUP_COL;
/
------------------------------------------------------------------------
CREATE OR REPLACE 
PACKAGE BODY DUP_COL IS

FUNCTION PIPE_DUP_COL(STR_COL IN VARCHAR2) RETURN DUP_COL_TBL PIPELINED AS
STR VARCHAR2(2000);
COMMA_POS NUMBER:=0;
START_POS NUMBER:=1;
I NUMBER:=0;
DUP_COL_ROW DUP_COL_REC;
BEGIN
STR:=STR_COL||',';
LOOP
  COMMA_POS := INSTR(STR, ',', START_POS);
  EXIT WHEN COMMA_POS = 0;
  DUP_COL_ROW.DUP_COL:=UPPER(SUBSTR(STR, START_POS, COMMA_POS - START_POS));  
  START_POS := COMMA_POS + 1;  
  PIPE ROW(DUP_COL_ROW);
  I:=I+1;
END LOOP;

END PIPE_DUP_COL;
END DUP_COL;
/
------------------------------------------------------------------------
--Call the pipe function

SELECT DUP_COL FROM 
TABLE(DUP_COL.PIPE_DUP_COL('green,blue,yellow, purple,green,yellow,purple,George,William,green,purple,blue,William'))
GROUP BY DUP_COL
HAVING COUNT(1) >1;

------------------------------------------------------------------------
DUP_COL
---------
WILLIAM
GREEN
PURPLE
BLUE
YELLOW

Elapsed: 00:00:00.016

for i in 1..10000 loop

set timing on
BEGIN
for i in 1..10000 loop
  FOR REC IN (
  SELECT DUP_COL FROM 
TABLE(DUP_COL.PIPE_DUP_COL('green,blue,yellow, purple,green,yellow,purple,George,William,green,purple,blue,William'))
GROUP BY DUP_COL
HAVING COUNT(1) >1
  ) loop
 null;
 end loop;
 end loop;
 END;
/
------------------------------------------------------------------------
anonymous block completed
Elapsed: 00:00:03.203

SET TIMING ON

BEGIN
for i in 1..10000 loop
for rec in (with t as (
select 'GREEN,BLUE,YELLOW,PURPLE,GREEN,YELLOW,PURPLE,GEORGE,WILLIAM,GREEN,PURPLE,BLUE,WILLIAM' words from dual
)
select  word
from  t,
xmltable(
'
for $word in distinct-values(ora:tokenize($words,","))
where ora:matches($words,fn:concat(",",$word,",(.*,)?",$word,","))
return $word
'
passing ',' || words || ',' as "words"
columns
word varchar2(30) path '.'
)
) loop
null;
end loop;
end loop;
end;
/
------------------------------------------------------------------------ 
BEGIN
for i in 1..10000 loop
  FOR REC IN (
  SELECT DUP_COL FROM 
TABLE(DUP_COL.PIPE_DUP_COL('green,blue,yellow, purple,green,yellow,purple,George,William,green,purple,blue,William'))
GROUP BY DUP_COL
HAVING COUNT(1) >1
  ) loop
 null;
 end loop;
 end loop;
 END;
/
------------------------------------------------------------------------ 
anonymous block completed
Elapsed: 00:00:08.703
anonymous block completed
Elapsed: 00:00:02.672

SQL> declare
  2    start_time pls_integer;
  3    end_time   pls_integer;
  4  begin
  5    -- Littlefoot's query
  6    start_time := sys.dbms_utility.get_time();
  7    for i in 1..10000 loop
  8      for rec in (
  9        WITH test
 10           AS (SELECT 'green,blue,yellow, purple,green,yellow,purple,George,William,green,purple,blue,William'
 11                         col
 12                 FROM DUAL),
 13             pieces AS (    SELECT TRIM (REGEXP_SUBSTR (col,
 14                                                        '[^,]+',
 15                                                        1,
 16                                                        LEVEL))
 17                                     just_one
 18                             FROM test
 19                       CONNECT BY REGEXP_SUBSTR (col,
 20                                                '[^,]+',
 21                                                 1,
 22                                                 LEVEL)
 23                                     IS NOT NULL)
 24         SELECT just_one, COUNT (*) cnt
 25               FROM pieces
 26       GROUP BY just_One
 27         HAVING COUNT (*) > 1
 28      ) loop
 29        null;
 30      end loop;
 31    end loop;
 32    end_time := sys.dbms_utility.get_time();
 33    dbms_output.put_line ('Littlefoot''s solution: '||((end_time-start_time)/100)||' seconds');
 34    -- Michel's query
 35    start_time := sys.dbms_utility.get_time();
 36    for i in 1..10000 loop
 37      for rec in (
 38        WITH
 39          test AS (
 40            SELECT 'green,blue,yellow, purple,green,yellow,purple,George,William,green,purple,blue,William'
 41                       col
 42              FROM DUAL
 43          ),
 44          data as ( select ','||col||',' col from test ),
 45          pieces as (
 46            select trim(substr(col,
 47                               instr(col, ',', 1, level) + 1,
 48                               instr(col, ',', 1, level+1)
 49                               - instr(col, ',', 1, level) - 1)) just_one
 50            from data
 51            connect by level <= length(col)-length(replace(col,',',''))
 52          )
 53        SELECT just_one, COUNT (*) cnt
 54        FROM pieces
 55        GROUP BY just_One
 56        HAVING COUNT (*) > 1
 57      ) loop
 58        null;
 59      end loop;
 60    end loop;
 61    end_time := sys.dbms_utility.get_time();
 62    dbms_output.put_line ('Michel''s solution: '||((end_time-start_time)/100)||' seconds');
 63    -- bluefred 's query 1
 64    start_time := sys.dbms_utility.get_time();
 65    for i in 1..10000 loop
 66      for rec in (
 67        select REGxp
 68        from (
 69        SELECT ExtractValue(column_value, '/element') REGxp
 70        FROM   TABLE
 71        (XMLSEQUENCE
 72        (XMLTYPE
 73        ('<list><element>'
 74        || replace ('GREEN,BLUE,YELLOW,PURPLE,GREEN,YELLOW,PURPLE,GEORGE,WILLIAM,GREEN,PURPLE,BLUE,WILLIAM',
 75        ',', '</element><element>')
 76        || '</element></list>').extract ('/list/*')))
 77        ) group by REGxp
 78        having count(1)>1
 79      ) loop
 80        null;
 81      end loop;
 82    end loop;
 83    end_time := sys.dbms_utility.get_time();
 84    dbms_output.put_line ('bluefred''s solution 1: '||((end_time-start_time)/100)||' seconds');
 85    -- Solomon's query
 86    start_time := sys.dbms_utility.get_time();
 87    for i in 1..10000 loop
 88      for rec in (
 89        with t as (
 90                   select 'GREEN,BLUE,YELLOW,PURPLE,GREEN,YELLOW,PURPLE,GEORGE,WILLIAM,GREEN,PURPLE,BLUE,WILLIAM' words from dual
 91                  )
 92        select  word
 93          from  t,
 94                xmltable(
 95                         '
 96                          for $word in distinct-values(ora:tokenize($words,","))
 97                          where ora:matches($words,fn:concat(",",$word,",(.*,)?",$word,","))
 98                           return $word
 99                         '
100                         passing ',' || words || ',' as "words"
101                         columns
102                           word varchar2(30) path '.'
103                        )
104      ) loop
105        null;
106      end loop;
107    end loop;
108    end_time := sys.dbms_utility.get_time();
109    dbms_output.put_line ('Solomon''s solution: '||((end_time-start_time)/100)||' seconds');
110    -- bluefred's query 2
111    start_time := sys.dbms_utility.get_time();
112    for i in 1..10000 loop
113      for rec in (
114        SELECT DUP_COL FROM
115        TABLE(DUP_COL.PIPE_DUP_COL('green,blue,yellow, purple,green,yellow,purple,George,William,green,purple,blue,William'))
116        GROUP BY DUP_COL
117        HAVING COUNT(1) >1
118      ) loop
119        null;
120      end loop;
121    end loop;
122    end_time := sys.dbms_utility.get_time();
123    dbms_output.put_line ('bluefred''s solution 2: '||((end_time-start_time)/100)||' seconds');
124  end;
125  /
Littlefoot's solution: 8.64 seconds
Michel's solution: 4.08 seconds
bluefred's solution 1: 83.64 seconds
Solomon's solution: 8.3 seconds
bluefred's solution: 2 8.36 seconds

PL/SQL procedure successfully completed.

SQL> /
Littlefoot's solution: 11.81 seconds
Michel's solution: 7.83 seconds
bluefred's solution 1: 84.09 seconds
Solomon's solution: 8.47 seconds
bluefred's solution 2: 7.93 seconds

PL/SQL procedure successfully completed.

SQL> /
Littlefoot's solution: 12.03 seconds
Michel's solution: 7.95 seconds
bluefred's solution 1: 84.83 seconds
Solomon's solution: 8.28 seconds
bluefred's solution 2: 8.03 seconds

PL/SQL procedure successfully completed.

SELECT CELL_NAME 
  FROM 
  ( 
        SELECT CELL_NAME CELL_NAME,COUNT(*) CNT
        FROM 
        (  
            SELECT trim(regexp_substr('green,blue,yellow, purple,green,yellow,purple,George,William,green,purple,blue,William', '[^,]+', 1, level)) CELL_NAME
              FROM dual
            CONNECT BY instr('green,blue,yellow, purple,green,yellow,purple,George,William,green,purple,blue,William', ',', 1, level - 1) > 0
        ) 
        GROUP BY CELL_NAME 
  )
  WHERE CNT>=2

SET TIMING ON
DECLARE 
START_TIME PLS_INTEGER;
end_time   pls_integer;
BEGIN
START_TIME := SYS.DBMS_UTILITY.GET_TIME();
LITTLEFOOT();
end_time := sys.dbms_utility.get_time();
dbms_output.put_line ('Littlefoot''s solution: '||((end_time-start_time)/100)||' seconds');

start_time := sys.dbms_utility.get_time();
MICHELCADOT();
End_Time := Sys.Dbms_Utility.Get_Time();
dbms_output.put_line ('Michel Cadot''s solution: '||((end_time-start_time)/100)||' seconds');

start_time := sys.dbms_utility.get_time();
SOLOMON_YAKOBSON();
End_Time := Sys.Dbms_Utility.Get_Time();
dbms_output.put_line ('Solomon Yakobson''s solution: '||((end_time-start_time)/100)||' seconds');

start_time := sys.dbms_utility.get_time();
BLUEFRED_XML();
End_Time := Sys.Dbms_Utility.Get_Time();
dbms_output.put_line ('Blue Fred''s XML solution: '||((end_time-start_time)/100)||' seconds');

start_time := sys.dbms_utility.get_time();
BLUEFRED_PIPE();
End_Time := Sys.Dbms_Utility.Get_Time();
dbms_output.put_line ('Blue Fred''s pipe solution: '||((end_time-start_time)/100)||' seconds');
END;
/
---------------------------------------------------
anonymous block completed
Elapsed: 00:00:52.860
Littlefoot's solution: 6.25 seconds
Michel Cadot's solution: 2.45 seconds
Solomon Yakobson's solution: 8.2 seconds
Blue Fred's XML solution: 32.96 seconds
Blue Fred's pipe solution: 2.97 seconds
---------------------------------------------------
anonymous block completed
Elapsed: 00:00:49.189
Littlefoot's solution: 6.44 seconds
Michel Cadot's solution: 2.56 seconds
Solomon Yakobson's solution: 8.61 seconds
Blue Fred's XML solution: 28.87 seconds
Blue Fred's pipe solution: 2.71 seconds
---------------------------------------------------
anonymous block completed
Elapsed: 00:00:47.361
Littlefoot's solution: 5.71 seconds
Michel Cadot's solution: 2.69 seconds
Solomon Yakobson's solution: 7.84 seconds
Blue Fred's XML solution: 28.36 seconds
Blue Fred's pipe solution: 2.75 seconds

anonymous block completed
Elapsed: 00:00:50.235
Littlefoot's solution: 5.76 seconds
Michel Cadot's solution: 2.46 seconds
Solomon Yakobson's solution: 8.18 seconds
Blue Fred's XML solution: 27.31 seconds
Blue Fred's pipe solution: 2.58 seconds
Vesile Taskiran's solution: 3.93 seconds
----------------------------------------------
anonymous block completed
Elapsed: 00:00:49.845
Littlefoot's solution: 5.6 seconds
Michel Cadot's solution: 2.41 seconds
Solomon Yakobson's solution: 7.4 seconds
Blue Fred's XML solution: 27.76 seconds
Blue Fred's pipe solution: 2.61 seconds
Vesile Taskiran's solution: 4.05 seconds