CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Sunil
Sunil

Reputation: 112

Oracle split by regex and aggregate again

I have a table from where I need to get only some part of record with comma after one part of record.

for example I have 

ABCD [1000-1987] BCD[101928-876] adgs[10987-786]

I want to get the record like :

1000-1987,101928-876,10987-786

Can you please help me out to get the record as mentioned.

Upvotes: 0

Views: 507

Answers (2)

Noel
Noel

Reputation: 10525

You don't have to split and then aggregate it. You can use regexp_replace to keep only those characters within square brackets, then replace the square brackets by comma.

WITH my_data
     AS (SELECT 'ABCD [1000-1987] BCD[101928-876] adgs[10987-786]' AS val
           FROM DUAL)
SELECT RTRIM (
          REPLACE (
              REGEXP_REPLACE (val, '(\[)(.*?\])|(.)', '\2'),
                  ']', ','),
          ',')
  FROM my_data;

Upvotes: 2

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

If you don't use 11g and do not want to use wm_concat:

WITH
  my_data AS (
    SELECT 'ABCD [1000-1987] BCD[101928-876] adgs[10987-786]' AS val FROM dual
)
SELECT
    ltrim(
      MAX(
        sys_connect_by_path(
          rtrim(ltrim(regexp_substr(val, '\[[0-9-]*\]', 1, level, NULL), '['), ']'),
          ',')
      ),
    ',') AS val_part
  FROM my_data
CONNECT BY regexp_substr(val, '\[[0-9-]*\]', 1, level, NULL) IS NOT NULL
;

If using wm_concat is ok for you:

WITH
  my_data AS (
    SELECT 'ABCD [1000-1987] BCD[101928-876] adgs[10987-786]' AS val FROM dual
)
SELECT
    wm_concat(rtrim(ltrim(regexp_substr(val, '\[[0-9-]*\]', 1, level, NULL), '['), ']')) AS val_part
  FROM my_data
CONNECT BY regexp_substr(val, '\[[0-9-]*\]', 1, level, NULL) IS NOT NULL
;

If you use 11g:

WITH
  my_data AS (
    SELECT 'ABCD [1000-1987] BCD[101928-876] adgs[10987-786]' AS val FROM dual
)
SELECT
    listagg(regexp_substr(val, '[a-b ]*\[([0-9-]*)\] ?', 1, level, 'i', 1), ',') WITHIN GROUP (ORDER BY 1) AS val_part
  FROM my_data
CONNECT BY regexp_substr(val, '[a-b ]*\[([0-9-]*)\] ?', 1, level, 'i', 1) IS NOT NULL
;

Read more about string aggregation techniques: Tim Hall about aggregation techniques

Read more about regexp_substr: regexp_substr - Oracle Documentation - 10g

Read more about regexp_substr: regexp_substr - Oracle Documentation - 11g

Upvotes: 4

Related Questions