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 (value) AS
SELECT '123 456'     FROM DUAL UNION ALL
SELECT '123-456 789' FROM DUAL;
2 rows affected

SELECT value,
       REGEXP_SUBSTR(value, '(^|[ -])([^ -]*)', 1, 1, null, 2) AS first,
       REGEXP_SUBSTR(value, '(^|[ -])([^ -]*)', 1, 2, null, 2) AS second,
       REGEXP_SUBSTR(value, '(^|[ -])([^ -]*)', 1, 3, null, 2) AS third
FROM   table_name


VALUE
FIRST
SECOND
THIRD


123 456
123
456
null

123-456 789
123
456
789


SELECT value,
       REGEXP_SUBSTR(value, '\d+', 1, 1) AS first,
       REGEXP_SUBSTR(value, '\d+', 1, 2) AS second,
       REGEXP_SUBSTR(value, '\d+', 1, 3) AS third
FROM   table_name


VALUE
FIRST
SECOND
THIRD


123 456
123
456
null

123-456 789
123
456
789


SELECT value,
       CASE
       WHEN sep1 > 0 THEN SUBSTR(value, 1, sep1 - 1)
       ELSE value
       END AS first,
       CASE
       WHEN sep2 > 0 THEN SUBSTR(value, sep1 + 1, sep2 - sep1 - 1)
       WHEN sep1 > 0 THEN SUBSTR(value, sep1+ 1)
       END AS second,
       CASE
       WHEN sep2 > 0 THEN SUBSTR(value, sep2 + 1)
       END AS third
FROM   (
  SELECT value,
         normalised_value,
         INSTR(normalised_value, ' ', 1, 1) AS sep1,
         INSTR(normalised_value, ' ', 1, 2) AS sep2
  FROM   (
    SELECT value,
           TRANSLATE(value, ' -', '  ') AS normalised_value
    FROM   table_name
  )
)


VALUE
FIRST
SECOND
THIRD


123 456
123
456
null

123-456 789
123
456
789