Compare Values of Two Comma Separated Strings in Oracle

There are times when we want to compare two Comma Separated Strings and wish to know how many values are matching in these two CSV Strings. I have created this Function which returns count of matching data in two comma separated strings.

SQL> CREATE OR REPLACE FUNCTION fn_match_csv (p_csv1         VARCHAR2,
  2                                           p_csv2         VARCHAR2,
  3                                           p_separator    VARCHAR2 DEFAULT ',')
  4    RETURN NUMBER
  5  IS
  6    l_cnt   NUMBER;
  7  BEGIN
  8
  9    WITH tab1  -- convert p_csv1 into rows and resolve as a temp table tab1
 10         AS (SELECT REGEXP_SUBSTR (p_csv1,
 11                                   '[^' || p_separator || ']+',
 12                                   1,
 13                                   LEVEL)
 14                    data
 15               FROM dual
 16            CONNECT BY LEVEL <= LENGTH (p_csv1) - LENGTH (REPLACE (p_csv1, p_separator)) + 1),
 17         tab2  -- convert p_csv2 into rows and resolve as a temp table tab1
 18         AS (SELECT REGEXP_SUBSTR (p_csv2,
 19                                   '[^' || p_separator || ']+',
 20                                   1,
 21                                   LEVEL)
 22                    data
 23               FROM dual
 24         CONNECT BY LEVEL <= LENGTH (p_csv2) - LENGTH (REPLACE (p_csv2, p_separator)) + 1)
 25    SELECT COUNT (*) -- get count of matching values
 26      INTO l_cnt
 27      FROM tab1, tab2
 28     WHERE tab1.data = tab2.data
 29           AND tab1.data IS NOT NULL
 30           AND tab2.data IS NOT NULL;
 31
 32     RETURN l_cnt;
 33
 34  END;
 35  /

Function created.

Lets check fn_match_csv function with CSV values. It should return 5.

SQL> select fn_match_csv(',Nimish,Puru,Rahul,Himanshu,Amit,Manoj,Rajat,Pankaj,Sachin,Anuj',
  2                      'Vishal,Nimish,Abhishek,Rahul,Puru,Atul,Nikhil,Sachin,Ankit,Anuj,')
  3                      csv_match_count
  4  from
  5     dual;

CSV_MATCH_COUNT
---------------
              5

Lets check fn_match_csv function with another CSV values separated by | (pipe). It should return 6.

SQL> select fn_match_csv('Vishal|Nimish|Abhishek|Rahul|Puru|Atul|Nikhil|Sachin|Ankit|Anuj|',
  2                      '|Nimish|Puru|Rahul|Nikhil|Amit|Manoj|Rajat|Pankaj|Sachin|Anuj',
  3                      '|')
  4         csv_match_count
  5  from
  6      dual;

CSV_MATCH_COUNT
---------------
              6

Related Posts:
- Comma Separated String To Rows 10g Onwards
- Comma Separated String To Rows using xmltable
- Nth Record from Comma Separated String
- Oracle: New String Aggregation Techniques