PL/SQL code to remove duplicates from a string

[Fred Victa]

I have a stored procedure written in PL/SQL and I have a variable that is defined as a VARCHAR2. This variable will contain a comma-delimited list of reasons why patients visit their doctor. How can I remove any duplicates in this variable?

[Tapas Chand]

How are you getting the comma-delimited string in SP?
Is it passed as input param or fetched from some table or built by appending comma in for loop over some data?

1. If it is passed as input param, this can be done before calling the SP.
2. If it is fetched from table, it can be done by first converting the comma delimited string into multiple rows and applying a DISTINCT on those rows. (Hint: Use regex).
3. If it is built by iteration, it can be done at the time of iteration only.

[Fred Victa]

Tapas Chand wrote:How are you getting the comma-delimited string in SP?
Is it passed as input param or fetched from some table or built by appending comma in for loop over some data?

1. If it is passed as input param, this can be done before calling the SP.
2. If it is fetched from table, it can be done by first converting the comma delimited string into multiple rows and applying a DISTINCT on those rows. (Hint: Use regex).
3. If it is built by iteration, it can be done at the time of iteration only.

It is not passed as an input parameter. It is getting it from a select statement that uses the LISTAGG function. I'm planning on making a change so that the select statement would do a bulk collect into a table variable and then I'll use the SET function to make a copy of the table variable and remove the duplicates.

[chris webster]

You should be able to do this in SQL - check out the answers on this StackOverflow post.