oracle - PL/SQL comma delimited list; remove dups and put in array

Question

I have a comma delimited list as a string in a PL/SQL procedure. I need to

1. Remove duplicates
2. Put the list in an array.

I have found multiple ways to do either, just not both. Any help?

Answer 1

There is a well-known SQL trick for turning comma-separated lists into rows. Just use that trick, add a DISTINCT keyword, and BULK COLLECT the results into your array (I assume you mean collection).

DECLARE
  p_test_string   VARCHAR2 (4000) := 'A,B,C,B,B,D';

  TYPE string_array_type IS TABLE OF VARCHAR2 (4000);

  l_array         string_array_type;
BEGIN
  SELECT DISTINCT REGEXP_SUBSTR (p_test_string,
                        '[^,]+',
                        1,
                        LEVEL)
  BULK   COLLECT INTO l_array
  FROM   DUAL
  CONNECT BY REGEXP_SUBSTR (p_test_string,
                            '[^,]+',
                            1,
                            LEVEL)
               IS NOT NULL
  ORDER BY 1;

  DBMS_OUTPUT.put_line ('l_array.count = ' || l_array.COUNT);
  DBMS_OUTPUT.put_line ('l_array(2) = ' || l_array (2));
END;

Output:

l_array.count = 4
l_array(2) = B