Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (id, value) AS
SELECT 1, 'IC123456IC789123' FROM DUAL UNION ALL
SELECT 2, 'IC123456IC123456' FROM DUAL;
2 rows affected

WITH rsqfc (id, value, idx) AS (
  SELECT id, value, 1 FROM table_name
UNION ALL
  SELECT id, value, idx + 1 FROM rsqfc WHERE idx + 1 <= LENGTH(value)/8
)
SELECT DISTINCT
       id,
       SUBSTR(value, 8*idx - 7, 8) AS value
FROM   rsqfc


ID
VALUE


1
IC123456

2
IC123456

1
IC789123


WITH rsqfc (id, value, spos, epos) AS (
  SELECT id,
         value,
         INSTR(value, 'IC', 1, 1),
         INSTR(value, 'IC', 1, 2)
  FROM   table_name
UNION ALL
  SELECT id,
         value,
         epos,
         INSTR(value, 'IC', epos + 2, 1)
  FROM   rsqfc
  WHERE  epos > 0
)
SELECT DISTINCT
       id,
       CASE epos
       WHEN 0
       THEN SUBSTR(value, spos)
       ELSE SUBSTR(value, spos, epos - spos)
       END AS value
FROM   rsqfc


ID
VALUE


1
IC123456

2
IC123456

1
IC789123


WITH patterns (id, value, pattern) AS (
  SELECT id,
         value,
         TRANSLATE(
           value,
           'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
           'XXXXXXXXXXXXXXXXXXXXXXXXXX0000000000'
         )
  FROM   table_name
),
rsqfc (id, value, pattern, spos, epos) AS (
  SELECT id,
         value,
         pattern,
         INSTR(pattern, 'X'),
         INSTR(pattern, 'X', INSTR(pattern, '0'))
  FROM   patterns
UNION ALL
  SELECT id,
         value,
         pattern,
         epos,
         INSTR(pattern, 'X', INSTR(pattern, '0', epos))
  FROM   rsqfc
  WHERE  epos > 0
)
SELECT DISTINCT
       id,
       CASE epos
       WHEN 0
       THEN SUBSTR(value, spos)
       ELSE SUBSTR(value, spos, epos - spos)
       END AS value
FROM   rsqfc


ID
VALUE


1
IC123456

2
IC123456

1
IC789123


WITH rsqfc (id, value, term, epos) AS (
  SELECT id,
         value,
         REGEXP_SUBSTR(value, '[A-Z]+\d+', 1, 1),
         REGEXP_INSTR(value, '[A-Z]+\d+', 1, 1, 1)
  FROM   table_name
UNION ALL
  SELECT id,
         value,
         REGEXP_SUBSTR(value, '[A-Z]+\d+', epos, 1),
         REGEXP_INSTR(value, '[A-Z]+\d+', epos, 1, 1)
  FROM   rsqfc
  WHERE  epos < LENGTH(value)
)
SELECT DISTINCT
       id,
       term AS value
FROM   rsqfc


ID
VALUE


1
IC123456

2
IC123456

1
IC789123