Oracle Regexp_Replace repetitive strings-CodePudding

Anyone help me tweak this pattern I need to capture repetitive strings next to each other (not other string apart) and keep only one of them. Here's my expression but I cannot get my desired output:

SELECT 
DATA,
REGEXP_REPLACE(DATA, '(.*)(.*)\1','\1\2') TRIMMED_STR
FROM
(
SELECT 'AAA-BBB-FFF-GGG' DATA FROM DUAL
UNION ALL
SELECT 'AAA-BBB-CCC-CCC-AAA-BBB-BBB' DATA FROM DUAL
UNION ALL
SELECT 'AAA-AAA-CCC-DDD-DDD-EEE-AAA' DATA FROM DUAL
UNION ALL
SELECT 'AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB' DATA FROM DUAL
UNION ALL
SELECT 'AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC' DATA FROM DUAL
UNION ALL
SELECT 'AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD' DATA FROM DUAL
) STR


OLD 
AAA-BBB-FFF-GGG
AAA-BBB-CCC-CCC-AAA-BBB-BBB
AAA-AAA-CCC-DDD-DDD-EEE-AAA
AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB
AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC
AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD

NEW
AAA-BBB-FFF-GGG
AAA-BBB-CCC-AAA-BBB
AAA-CCC-DDD-EEE-AAA
AAA-BBB-CCC-DDD-EEE-FFF-GGG-BBB
AAA-BBB-CCC-DDD-EEE-FFF-CCC
AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-JJJ-DDD

CodePudding user response：

Something like this?

SQL> with str as
  2  (
  3  select 'AAA-BBB-FFF-GGG' data from dual
  4  union all
  5  select 'AAA-BBB-CCC-CCC-AAA-BBB-BBB' data from dual
  6  union all
  7  select 'AAA-AAA-CCC-DDD-DDD-EEE-AAA' data from dual
  8  union all
  9  select 'AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB' data from dual
 10  union all
 11  select 'AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC' data from dual
 12  union all
 13  select 'AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD' data from dual
 14  )
 15  select data old,
 16    regexp_replace(data, '([^-] )(-[-]*\1) ', '\1') new
 17  from str;

OLD                                                NEW
-------------------------------------------------- --------------------------------------------------
AAA-BBB-FFF-GGG                                    AAA-BBB-FFF-GGG
AAA-BBB-CCC-CCC-AAA-BBB-BBB                        AAA-BBB-CCC-AAA-BBB
AAA-AAA-CCC-DDD-DDD-EEE-AAA                        AAA-CCC-DDD-EEE-AAA
AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB            AAA-BBB-CCC-DDD-EEE-FFF-GGG-BBB
AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC                AAA-BBB-CCC-DDD-EEE-FFF-CCC
AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD    AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-JJJ-DDD

6 rows selected.

SQL>

CodePudding user response：

You can use:

WITH table_name (data) AS (
  SELECT 'AAA-BBB-FFF-GGG'                                 FROM DUAL UNION ALL
  SELECT 'AAA-BBB-CCC-CCC-AAA-BBB-BBB'                     FROM DUAL UNION ALL
  SELECT 'AAA-AAA-CCC-DDD-DDD-EEE-AAA'                     FROM DUAL UNION ALL
  SELECT 'AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB'         FROM DUAL UNION ALL
  SELECT 'AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC'             FROM DUAL UNION ALL
  SELECT 'AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD' FROM DUAL
)
SELECT data,
       REGEXP_REPLACE(
         data,
         '(^|-)(.*?)-\2(-|$)',
         '\1\2\3'
       ) AS trimmed_str
FROM   table_name

Which outputs:

DATA TRIMMED_STR

AAA-BBB-FFF-GGG

AAA-BBB-CCC-CCC-AAA-BBB-BBB AAA-BBB-CCC-AAA-BBB

AAA-AAA-CCC-DDD-DDD-EEE-AAA AAA-CCC-DDD-EEE-AAA

AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB AAA-BBB-CCC-DDD-EEE-FFF-GGG-BBB

AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC AAA-BBB-CCC-DDD-EEE-FFF-FFF-CCC

AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-JJJ-DDD

DATA	TRIMMED_STR
AAA-BBB-FFF-GGG
AAA-BBB-CCC-CCC-AAA-BBB-BBB	AAA-BBB-CCC-AAA-BBB
AAA-AAA-CCC-DDD-DDD-EEE-AAA	AAA-CCC-DDD-EEE-AAA
AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB	AAA-BBB-CCC-DDD-EEE-FFF-GGG-BBB
AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC	AAA-BBB-CCC-DDD-EEE-FFF-FFF-CCC
AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD	AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-JJJ-DDD

db<>fiddle here