如何在oracle pl / sql中获取varchar变量的uniqe值

4 回答

• 3

  您可以先split your string，使用 DISTINCT 过滤重复项并使用LISTAGG（例如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
  );
  

  这回来了
  aa;bb;cc;dd;ee

  回复于 2024-04-25T19:04:53+08:00
• 0

  如果是oracle 11g及更高版本，您可以使用它

  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)
  

  回复于 2024-04-25T19:04:53+08:00
• 1

  和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
  

  回复于 2024-04-25T19:04:53+08:00
• 1

  您可以通过使用REGEXP和WMSYS.WM_CONCAT（即使其未记录的函数）或LISTAGG来轻松实现此目的 . 请参阅下面的代码段 . 希望这可以帮助 .

  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;
  

  回复于 2024-04-25T19:04:53+08:00