• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PL/SQL code to remove duplicates from a string

 
Ranch Hand
Posts: 226
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 226
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You should be able to do this in SQL - check out the answers on this StackOverflow post.
 
reply
    Bookmark Topic Watch Topic
  • New Topic