Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test_data ( value ) AS
SELECT 'ALLIANZ VERSICHERUNGS-AG VERTRAG AS-9028000568 KFZ-VERSICHERUNG KFZ-VERS. XX-Y 427 01.01.19 - 31.12.19' FROM DUAL UNION ALL
SELECT 'AXA VERSICHERUNG AG 40301089910 KFZ HAFTPFLICHT ABC-RM10 37,35 + 40330601383 KFZ HAFTPFLIVHT ABC-LX 283 21,19' FROM DUAL UNION ALL
SELECT 'AA-A1BB-BB222CC C3333' FROM DUAL UNION ALL
SELECT 'AA-A1 AA-A 1 aa a1' FROM DUAL
4 rows affected

WITH items ( value, item, next_pos ) AS (
  SELECT value,
         TRANSLATE( UPPER( REGEXP_SUBSTR( value, '([^a-z]|^)([a-z]{1,3}[- ][a-z]{1,2} ?\d{1,4})(\D|$)', 1, 1, 'i', 2 ) ), '_ -', '_' ),
         REGEXP_INSTR(  value, '([^a-z]|^)([a-z]{1,3}[- ][a-z]{1,2} ?\d{1,4})(\D|$)', 1, 1, 1, 'i', 2 ) - 1
  FROM   test_data
UNION ALL
  SELECT value,
         TRANSLATE( UPPER( REGEXP_SUBSTR( value, '([^a-z]|^)([a-z]{1,3}[- ][a-z]{1,2} ?\d{1,4})(\D|$)', next_pos, 1, 'i', 2 ) ), '_ -', '_' ),
         REGEXP_INSTR(  value, '([^a-z]|^)([a-z]{1,3}[- ][a-z]{1,2} ?\d{1,4})(\D|$)', next_pos, 1, 1, 'i', 2 ) - 1
  FROM   items
  WHERE  next_pos > 0
)
SELECT item,
       COUNT(*)
FROM   items
WHERE  item IS NOT NULL AND NEXT_POS > 0
GROUP BY item


ITEM
COUNT(*)


CCC3333
1

AAA1
4

XXY427
1

ABCRM10
1

ABCLX283
1

BBBB222
1


WITH items ( rid, value, item, next_pos ) AS (
  SELECT ROWID,
         value,
         TRANSLATE( UPPER( REGEXP_SUBSTR( value, '([^a-z]|^)([a-z]{1,3}[- ][a-z]{1,2} ?\d{1,4})(\D|$)', 1, 1, 'i', 2 ) ), '_ -', '_' ),
         REGEXP_INSTR(  value, '([^a-z]|^)([a-z]{1,3}[- ][a-z]{1,2} ?\d{1,4})(\D|$)', 1, 1, 1, 'i', 2 ) - 1
  FROM   test_data
UNION ALL
  SELECT rid,
         value,
         TRANSLATE( UPPER( REGEXP_SUBSTR( value, '([^a-z]|^)([a-z]{1,3}[- ][a-z]{1,2} ?\d{1,4})(\D|$)', next_pos, 1, 'i', 2 ) ), '_ -', '_' ),
         REGEXP_INSTR(  value, '([^a-z]|^)([a-z]{1,3}[- ][a-z]{1,2} ?\d{1,4})(\D|$)', next_pos, 1, 1, 'i', 2 ) - 1
  FROM   items
  WHERE  next_pos > 0
)
SELECT LISTAGG( item, ' + ' ) WITHIN GROUP ( ORDER BY item ) AS items,
       COUNT(*)
FROM   (
  SELECT DISTINCT
         rid,
         item
  FROM   items
  WHERE  item IS NOT NULL AND NEXT_POS > 0
)
GROUP BY rid;


ITEMS
COUNT(*)


XXY427
1

ABCLX283 + ABCRM10
2

AAA1 + BBBB222 + CCC3333
3

AAA1
1