Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH test_data (value) AS (
  SELECT 'Big_Dog_0044_0080' FROM DUAL UNION ALL
  SELECT 'Big_Dog_00440080' FROM DUAL UNION ALL
  SELECT 'Big_Dog_00440A80' FROM DUAL
)
SELECT value,
       REGEXP_SUBSTR(value, '([0-9]+)_?([0-9A-Z]*)$', 1, 1, NULL, 1) AS Group1 , 
       REGEXP_SUBSTR(value, '([0-9]+)_?([0-9A-Z]*)$', 1, 1, NULL, 2) AS Group2
FROM   test_data;


VALUE
GROUP1
GROUP2


Big_Dog_0044_0080
0044
0080

Big_Dog_00440080
00440080
null

Big_Dog_00440A80
00440
A80


WITH test_data (value) AS (
  SELECT 'Big_Dog_0044_0080' FROM DUAL UNION ALL
  SELECT 'Big_Dog_00440080' FROM DUAL UNION ALL
  SELECT 'Big_Dog_00440A80' FROM DUAL
)
SELECT value,
       REGEXP_SUBSTR(value, '([0-9]{4})_?([0-9A-Z]{4})$', 1, 1, NULL, 1) AS Group1 , 
       REGEXP_SUBSTR(value, '([0-9]{4})_?([0-9A-Z]{4})$', 1, 1, NULL, 2) AS Group2
FROM   test_data;


VALUE
GROUP1
GROUP2


Big_Dog_0044_0080
0044
0080

Big_Dog_00440080
0044
0080

Big_Dog_00440A80
0044
0A80


WITH test_data (value) AS (
  SELECT 'Big_Dog_0044_0080' FROM DUAL UNION ALL
  SELECT 'Big_Dog_00440080' FROM DUAL UNION ALL
  SELECT 'Big_Dog_00440A80' FROM DUAL UNION ALL
  SELECT 'A_CatWeezle' FROM DUAL
)
SELECT value,
       CASE
       WHEN REGEXP_LIKE(value, '^Big_Dog_(\d{4})_?([0-9A-Z]{4})$')
       THEN REGEXP_SUBSTR(value, '^Big_Dog_(\d{4})_?([0-9A-Z]{4})$', 1, 1, NULL, 1)
       WHEN REGEXP_LIKE(value, '^([A-C])_CatWeezle$')
       THEN REGEXP_SUBSTR(value, '^([A-C])_CatWeezle$', 1, 1, NULL, 1)
       END AS group1,
       CASE
       WHEN REGEXP_LIKE(value, '^Big_Dog_(\d{4})_?([0-9A-Z]{4})$')
       THEN REGEXP_SUBSTR(value, '^Big_Dog_(\d{4})_?([0-9A-Z]{4})$', 1, 1, NULL, 2)
       END AS group2
FROM   test_data;


VALUE
GROUP1
GROUP2


Big_Dog_0044_0080
0044
0080

Big_Dog_00440080
0044
0080

Big_Dog_00440A80
0044
0A80

A_CatWeezle
A
null
VALUE	GROUP1	GROUP2
Big_Dog_0044_0080	0044	0080
Big_Dog_00440080	00440080	null
Big_Dog_00440A80	00440	A80