0

How can I get unique values of a varchar variable in oracle pl/sql? I have a variable like this:

aa;bb;aa;cc;dd;ee;dd

I would like to get the unique values still separated by ";". Desired result:

aa;bb;cc;dd;ee

Is this possible? Thanks.

triplus
  • 91
  • 5
  • 17
  • 2
    While this is possible, it's really not desirable at all to store your data in CSV (SSV, semicolon separated values) format, because it renders your database impotent to help you query results. So I vote for changing your database design. – Tim Biegeleisen Mar 21 '16 at 15:21

4 Answers4

3

You can first split your string, use DISTINCT to filter duplicates and combine it again using LISTAGG (example at SQL Fiddle).

SELECT LISTAGG(x, ';') WITHIN GROUP(ORDER BY x)
FROM (
  SELECT DISTINCT regexp_substr('aa;bb;aa;cc;dd;ee;dd', '[^;]+', 1, LEVEL) AS x
  FROM dual
  CONNECT BY regexp_substr('aa;bb;aa;cc;dd;ee;dd', '[^;]+', 1, LEVEL) IS NOT NULL
);

This returns
aa;bb;cc;dd;ee

Community
  • 1
  • 1
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
1

In case of oracle 11g and greater you can use this

SELECT listagg(val,';') WITHin GROUP(ORDER BY NULL) 
FROM 
(SELECT DISTINCT REGEXP_SUBSTR('aa;bb;aa;cc;dd;ee;dd', '[^;]+', 1, LEVEL) val
   FROM dual
CONNECT BY REGEXP_SUBSTR('aa;bb;aa;cc;dd;ee;dd', '[^;]+', 1, LEVEL) IS NOT NULL)
Ilia Maskov
  • 1,858
  • 16
  • 26
1

And xmlquery. 

select xmlcast( xmlquery('let $i  := string-join(distinct-values(ora:tokenize($doc,";")),";") return  $i' passing 'aa;bb;aa;cc;dd;ee;dd' as "doc" returning content) as varchar2(4000)) from dual
Arkadiusz Łukasiewicz
  • 6,241
  • 1
  • 11
  • 17
0

You can easily achieve this by using REGEXP and WMSYS.WM_CONCAT(even though its undocumented function ) or LISTAGG to chive this. Please see below snippet. Hope this helps.

SELECT wm_concat(DISTINCT regexp_substr('aa;bb;aa;cc;dd;ee;dd','[^;]+', 1, level))
FROM dual
  CONNECT BY regexp_substr('aa;bb;aa;cc;dd;ee;dd', '[^;]+', 1, level) IS NOT NULL;
Avrajit Roy
  • 3,233
  • 1
  • 13
  • 25
  • 1
    Please don't use wm_concat. It only supports comma as a delimiter whereas OP wants a semicolon. It is an undocumented function that is only used by oracle [internally](https://support.oracle.com/epmos/faces/DocumentDisplay?id=1336219.1). And most of all, it is discontinued in 12c. – ruudvan Mar 21 '16 at 20:42