How to remove duplicates from a comma separated list in regex in Oracle regexp_replace?

Question

I have

'VA - HRD 1, VA - HRD 1, VA - NOVA 1, VA - NOVA 1'

and want to get

'VA - HRD 1, VA - NOVA 1'

I'm trying to

regexp_replace( 'VA - HRD 1, VA - HRD 1, VA - NOVA 1, VA - NOVA 1' ,'([^,]+)(,\1)+', '\1')

but it does not delete all duplicates, it produces: VA - HRD 1, VA - HRD 1, VA - NOVA 1

Please, help...

Answer 1

([^,]+)(,[ ]*\1)+

Try it. These work. Watch the demo.

http://regex101.com/r/yG7zB9/8

The problem was VA - HRD 1, VA - HRD 1

                     ^  ^

The space is here. You did not take this into account since the first match does not take place. It is inlcde [ ]*or \s*to take it.

Answer 2

Select replace(REGEXP_REPLACE('1234567890,2220006789,2220006789,477-000-6789','([^,]+)(,[ ]*\1)+'),',,',',') 
As [columnName] From [tableName]