I have a comma delimited list as a string in a PL/SQL procedure. I need to
- Remove duplicates
- Put the list in an array.
I have found multiple ways to do either, just not both. Any help?
See Question&Answers more detail:osI have a comma delimited list as a string in a PL/SQL procedure. I need to
I have found multiple ways to do either, just not both. Any help?
See Question&Answers more detail:osThere 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