title: Oracle SQL Repeated words in the String
body: <p>I need your suggestions/inputs on of the following task. I have the following table:</p>
  
  <pre><code>ID      ID_NAME                             
  ------ ---------------------------------   
  1       TOM HANKS TOM JR                    
  2       PETER PATROL PETER JOHN PETER       
  3       SAM LIVING                          
  4       JOHNSON &amp; JOHNSON INC               
  5       DUHGT LLC                              
  6       THE POST OF THE OFFICE              
  7       TURNING REP WEST                    
  8       GEORGE JOHN                         
  </code></pre>
  
  <p>I Need a SQL query to find a repetitive word for every ID. if it exists, i need to get the count of the repeated word.
  For instance in ID 2, the word PETER was repeated 3 times and in ID 1 the word TOM was repeated twice. so I need the output something like this:</p>
  
  <pre><code>ID      ID_NAME                             COUNT
  ------ ---------------------------------    --------
  1       TOM HANKS TOM JR                    2
  2       PETER PATROL PETER JOHN PETER       3
  3       SAM LIVING                          0
  4       JOHNSON &amp; JOHNSON INC               2
  5       DUHGT LLC                           0    
  6       THE POST OF THE OFFICE              2
  7       TURNING REP WEST                    0
  8       GEORGE JOHN                         0
  </code></pre>
  
  <p>Just an FYI, The table has 560K rows</p>
  
  <p>I tried the below and it didn't work and it is literally looking for every single word. </p>
  
  <pre><code>SELECT RESULT, COUNT(*)
      FROM (SELECT
              REGEXP_SUBSTR(COL_NAME, '[^ ]+', 1, COLUMN_VALUE) RESULT
            FROM TABLE_NAME T ,
                 TABLE(CAST(MULTISET(SELECT DISTINCT LEVEL
                                     FROM TABLE_NAME X
                CONNECT BY LEVEL &lt;= LENGTH(X.COL_NAME) - LENGTH(REPLACE(X.COL_NAME, ' ', '')) + 1
                                    ) AS SYS.ODCINUMBERLIST)) T1
            )
     WHERE RESULT IS NOT NULL
     GROUP BY RESULT
     ORDER BY 1;
  </code></pre>
  
  <p>Please let me know your inputs.</p>