Home » SQL & PL/SQL » SQL & PL/SQL » REQEXP_SUBTR debug (11i)
REQEXP_SUBTR debug [message #677031] Thu, 08 August 2019 23:59 Go to next message
Mani Balan
Messages: 1
Registered: August 2019
Junior Member
Hi ,

Need help on separating the value from the below string.

,XX_ES_TRADE_144,0,168,"STANDARD,CREDIT,DEBIT,MIXED",,,,,,,,,,,,,,,"D1416,D2018,D2616,D2636,D3018,D3646,D5008,D5010,D5012",,,,,,,, "ESBCN,ESTAR",,,,,GPAU,,,,,,,,TRADE,,APPRO,,,,,,,

My existing rule was v_col_text:=REGEXP_SUBSTR(v_buffer,'(([^,^\"])*(\".*\")*([^,^\"])*)(,)',1,1);

Expected output is :

REGEXP_SUBSTR(v_buffer,'(([^,^\"])*(\".*\")*([^,^\"])*)(,)',1,2) ==> result should be XX_ES_TRADE_144
REGEXP_SUBSTR(v_buffer,'(([^,^\"])*(\".*\")*([^,^\"])*)(,)',1,3) ==> result should be 0
REGEXP_SUBSTR(v_buffer,'(([^,^\"])*(\".*\")*([^,^\"])*)(,)',1,4) ==> result should be 0168
REGEXP_SUBSTR(v_buffer,'(([^,^\"])*(\".*\")*([^,^\"])*)(,)',1,5) ==> result should be STANDARD,CREDIT,DEBIT,MIXED
REGEXP_SUBSTR(v_buffer,'(([^,^\"])*(\".*\")*([^,^\"])*)(,)',1,6) ==> result should be null

can you please assist on this
Re: REQEXP_SUBTR debug [message #677034 is a reply to message #677031] Fri, 09 August 2019 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's one way:
SQL> with
  2    data as (
  3      select ',XX_ES_TRADE_144,0,168,"STANDARD,CREDIT,DEBIT,MIXED",,,,,,,,,,,,,,,"D1416,D2018,D2616,D2636,D3018,D3646,D5008,D5010,D5012",,,,,,,, "ESBCN,ESTAR",,,,,GPAU,,,,,,,,TRADE,,APPRO,,,,,,,' data
  4      from dual
  5    ),
  6    vals as (
  7      select column_value nb,
  8             decode(mod(column_value,2),
  9                    1, trim(both ' ' from regexp_substr(data, '[^"]+', 1, column_value)),
 10                    '"'||replace(regexp_substr(data, '[^"]+', 1, column_value), ',', '@')||'"'
 11                   ) val
 12      from data,
 13           table(cast(multiset(select level from dual
 14                               connect by level <= regexp_count(data,'[^"]+'))
 15                 as sys.odciNumberList))
 16    ),
 17    enclosed as (
 18      select nb,
 19             decode(substr(val,1,1), '"', val, trim(both '"' from replace(val, ',', '","'))) val
 20      from vals
 21    ),
 22    modified as (
 23      select regexp_replace(listagg(val, '') within group (order by nb), '^,', '"",') data
 24      from enclosed
 25    ),
 26    split as (
 27      select column_value nb, regexp_substr(data,'[^,]+', 1, column_value) elem
 28      from modified,
 29           table(cast(multiset(select level from dual
 30                               connect by level <= regexp_count(data,',')+1)
 31                 as sys.odciNumberList))
 32    )
 33  select nb, trim(both '"' from replace(elem, '@', ',')) elem
 34  from split
 35  order by nb
 36  /
        NB ELEM
---------- ------------------------------------------------------------
         1
         2 XX_ES_TRADE_144
         3 0
         4 168
         5 STANDARD,CREDIT,DEBIT,MIXED
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20 D1416,D2018,D2616,D2636,D3018,D3646,D5008,D5010,D5012
        21
        22
        23
        24
        25
        26
        27
        28 ESBCN,ESTAR
        29
        30
        31
        32
        33 GPAU
        34
        35
        36
        37
        38
        39
        40
        41 TRADE
        42
        43 APPRO
        44
        45
        46
        47
        48
        49
        50

50 rows selected.

[Updated on: Fri, 09 August 2019 02:15]

Report message to a moderator

Re: REQEXP_SUBTR debug [message #677035 is a reply to message #677034] Fri, 09 August 2019 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another way:
SQL> with
  2    data as (
  3      select ',XX_ES_TRADE_144,0,168,"STANDARD,CREDIT,DEBIT,MIXED",,,,,,,,,,,,,,,"D1416,D2018,D2616,D2636,D3018,D3646,D5008,D5010,D5012",,,,,,,, "ESBCN,ESTAR",,,,,GPAU,,,,,,,,TRADE,,APPRO,,,,,,,' data
  4      from dual
  5    ),
  6    vals as (
  7      select column_value nb,
  8             decode(mod(column_value,2),
  9                    1, trim(both '"'
 10                            from replace(
 11                                   trim(both ' '
 12                                        from regexp_substr(data, '[^"]+', 1, column_value)),
 13                                   ',', '","')),
 14                    '"'||regexp_substr(data, '[^"]+', 1, column_value)||'"'
 15                   ) val
 16      from data,
 17           table(cast(multiset(select level from dual
 18                               connect by level <= regexp_count(data,'[^"]+'))
 19                 as sys.odciNumberList))
 20    ),
 21    modified as (
 22      select nb,
 23             case
 24               when nb = 1 and substr(val,1,1) = ',' then '""' || rtrim(val ,',')
 25               when nb = max(nb) over() and substr(val,-1,1) = ',' then ltrim(val,',') || '""'
 26               else trim(both ',' from val)
 27             end val
 28      from vals
 29    ),
 30    result as (
 31      select nb, 0 nb2,  val from modified where mod(nb,2) = 0
 32      union all
 33      select nb, column_value,
 34             regexp_substr(val, '[^,]+', 1, column_value) val
 35      from (select nb, val from modified where mod(nb,2) = 1),
 36           table(cast(multiset(select level from dual
 37                               connect by level <= regexp_count(val,',')+1)
 38                 as sys.odciNumberList))
 39    )
 40  select row_number() over (order by nb, nb2) nb,
 41         trim(both '"' from val) val
 42  from result
 43  order by 1
 44  /
        NB VAL
---------- --------------------------------------------------------------------------------
         1
         2 XX_ES_TRADE_144
         3 0
         4 168
         5 STANDARD,CREDIT,DEBIT,MIXED
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20 D1416,D2018,D2616,D2636,D3018,D3646,D5008,D5010,D5012
        21
        22
        23
        24
        25
        26
        27
        28 ESBCN,ESTAR
        29
        30
        31
        32
        33 GPAU
        34
        35
        36
        37
        38
        39
        40
        41 TRADE
        42
        43 APPRO
        44
        45
        46
        47
        48
        49
        50

50 rows selected.
Re: REQEXP_SUBTR debug [message #677058 is a reply to message #677035] Tue, 13 August 2019 14:15 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Previous Topic: Union and sort the data based on date range
Next Topic: SQL Tuning instead of Cross Join
Goto Forum:
  


Current Time: Thu Mar 28 18:22:28 CDT 2024