1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Regular expression

Discussion in 'SQL PL/SQL' started by Samyms, Nov 20, 2015.

  1. Samyms

    Samyms Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Chennai
    I want to remove duplicate record from Listagg function.


    Combination of duplicate Value comes as
    1) PAY,PAY,PAY,PAY
    2) ACP,PAY,PAY
    3) PAY,ACP,ACP
    4) ACP,ACP
    Code: [Select all] [Show/ hide]
    create table paym (method varchar2(100));

    insert into paym values('PAY,PAY,PAY,PAY');
    insert into paym values('ACP,PAY,PAY');
    insert into paym values('PAY,ACP,ACP');
    insert into paym values('PAY,PAY');

    SELECT method ,
    COUNT(*) AS method_cnt ,
    LISTAGG(method, ',') WITHIN GROUP (ORDER BY method) AS List_Agg,
    REGEXP_REPLACE(LISTAGG(method, ',') WITHIN GROUP (ORDER BY method),'([^,]+)(,\1)+', '\1') AS List_Agg_Distinct
    FROM paym
    GROUP BY method


    Its not removing the duplicate correctly. Can you please help me.
    Output should be :
    1. PAY
    2. ACP,PAY
    3. PAY,ACP
    4. ACP
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    for example :

    Code (SQL):
    WITH
      paym (method) AS
    ( SELECT 'PAY,PAY,PAY,PAY'  FROM dual UNION ALL
      SELECT  'ACP,PAY,PAY'  FROM dual UNION ALL
      SELECT  'PAY,ACP,ACP' FROM dual UNION ALL
      SELECT  'ACP,ACP' FROM dual
    )
    SELECT p.method,listagg(z.column_value,',') WITHIN GROUP(ORDER BY NULL)  agg
    FROM paym p,
                  TABLE
                  (
                      CAST
                      (
                          multiset
                          (
                              SELECT
                                  DISTINCT regexp_substr(p.method,'[^,]+',1,level)
                              FROM dual
                              CONNECT BY
                                  regexp_substr(p.method,'[^,]+',1,level) IS NOT NULL
                           )
                               AS sys.odcivarchar2list
                      )
                  ) z
    GROUP BY method;

    SQL>
    METHOD  AGG
    --------------- --------------------------------------------------------------------------------
    ACP,ACP  ACP
    ACP,PAY,PAY  ACP,PAY
    PAY,ACP,ACP  ACP,PAY
    PAY,PAY,PAY,PAY PAY