You can use:

SELECT CASE
       WHEN INSTR(value, '-') > 0
       THEN SUBSTR(value, INSTR(value, '-') + 1)
       END AS subject
FROM   table_name

or

SELECT REGEXP_SUBSTR( value, '-(.*)$', 1, 1, NULL, 1 ) AS subject
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'VP3 - Art & Design and HS5 - Health & Social Care' FROM DUAL UNION ALL
SELECT '1-2-3' FROM DUAL UNION ALL
SELECT '123456' FROM DUAL

Both output:

| SUBJECT                                      |
| :------------------------------------------- |
|  Art & Design and HS5 - Health & Social Care |
| 2-3                                          |
| null                                         |

Trimming leading white-space:

If you want to trim the leading white-space then you can use:

SELECT CASE
       WHEN INSTR(value, '-') > 0
       THEN LTRIM(SUBSTR(value, INSTR(value, '-') + 1))
       END AS subject
FROM   table_name

or

SELECT REGEXP_SUBSTR( value, '-\s*(.*)$', 1, 1, NULL, 1 ) AS subject
FROM   table_name

Which both output:

| SUBJECT                                     |
| :------------------------------------------ |
| Art & Design and HS5 - Health & Social Care |
| 2-3                                         |
| null                                        |

Why the naive solutions don't always work:

SELECT SUBSTR(value, INSTR(value, '-') + 2) AS subject
FROM   table_name

Does not work in 2 cases:

• It finds the index of the - character and then skips 2 characters (the - character and then the assumed white-space character); if the second character is not a white-space character then it will miss the first character of the substring (i.e. if the input is 1-2-3 then the output would be -3 rather than 2-3).
• It assumes that there will always be a - character in the string; if this is not the case then it will erroneously return the substring starting from the second character rather than returning NULL (i.e. if the input is 123456 then the output is 23456 rather than NULL).

Using the regular expression:

SELECT REGEXP_SUBSTR(value, '[^-]+', 1, 2)
FROM   table_name

Does not find the substring after the 1st - character; it will find the substring between the 1st and 2nd - characters and strip any characters outside that range (inclusive of the - characters). So if the input is VP3 - Art & Design and HS5 - Health & Social Care then the output is Art & Design and HS5 rather than the expected Art & Design and HS5 - Health & Social Care.