这两行成功地将主题和课程编号提取到单独的字段中：

,REGEXP_SUBSTR(instance_name,'^[A-Z]{2,4}') ISU_SUBJECT
,REGEXP_SUBSTR(instance_name, REGEXP_SUBSTR(instance_name,'^[A-Z]{2,4} (\d{4})', 1, 1, NULL, 1)) ISU_Course_Number

如果所有行看起来都像您作为示例数据发布的行，则：如果你可以使用简单快速的substr + instr组合，为什么要把事情变得复杂和潜在的缓慢呢？
将所有内容提取到第二个空格：

SQL> select instance_name,
  2    substr(instance_name, 1, instr(instance_name, ' ', 1, 2) - 1) result
  3  from test;

INSTANCE_NAME                                            RESULT
-------------------------------------------------------- ----------
MATH 1175 Calculus II                                    MATH 1175
MATH 1153 Statistical Reasoning                          MATH 1153
ENGL 1101 Writing and Rhetoric I                         ENGL 1101
FIN 1115 Personal Finance                                FIN 1115
SPAN 1102 Elementary Spanish II                          SPAN 1102
POLS 1102 Intro to Politics Critical Thinking & Analysis POLS 1102
MA 0104 Intro to Medical Assisting Administrative        MA 0104
HO 0107 Medical Law and Ethics                           HO 0107

8 rows selected.

SQL>

如果你需要将值分成两列（我不明白你想要两列），也没有问题。
仍然是substr + instr（尽管现在有点复杂）：

SQL> select instance_name,
  2    substr(instance_name, 1, instr(instance_name, ' ', 1, 1) - 1) subject,
  3    substr(instance_name,
  4           instr(instance_name, ' ', 1, 1) + 1,
  5           instr(instance_name, ' ', 1, 2) - instr(instance_name, ' ', 1, 1) - 1) code
  6  from test;

INSTANCE_NAME                                            SUBJECT    CODE
-------------------------------------------------------- ---------- ----------
MATH 1175 Calculus II                                    MATH       1175
MATH 1153 Statistical Reasoning                          MATH       1153
ENGL 1101 Writing and Rhetoric I                         ENGL       1101
FIN 1115 Personal Finance                                FIN        1115
SPAN 1102 Elementary Spanish II                          SPAN       1102
POLS 1102 Intro to Politics Critical Thinking & Analysis POLS       1102
MA 0104 Intro to Medical Assisting Administrative        MA         0104
HO 0107 Medical Law and Ethics                           HO         0107

8 rows selected.

或者，简单的正则表达式：从instance_name中获取第一个和第二个 * 字 *。它们是什么数据类型并不重要，这是源字符串的前两部分：

SQL> select instance_name,
  2    regexp_substr(instance_name, '\w+', 1, 1) subject,
  3    regexp_substr(instance_name, '\w+', 1, 2) code
  4  from test;

INSTANCE_NAME                                            SUBJECT    CODE
-------------------------------------------------------- ---------- ----------
MATH 1175 Calculus II                                    MATH       1175
MATH 1153 Statistical Reasoning                          MATH       1153
ENGL 1101 Writing and Rhetoric I                         ENGL       1101
FIN 1115 Personal Finance                                FIN        1115
SPAN 1102 Elementary Spanish II                          SPAN       1102
POLS 1102 Intro to Politics Critical Thinking & Analysis POLS       1102
MA 0104 Intro to Medical Assisting Administrative        MA         0104
HO 0107 Medical Law and Ethics                           HO         0107

8 rows selected.

SQL>