所以如果值是“ABC1234”，那么我需要把ABC和1234分开。但是如果只有“ABC”作为值，我只需要“ABC”。
在其他解决方案中，我提出一个解决方案，如下所示：

逻辑：

1)将所有数字替换为1。检查digit出现在string中的位置。如果字符串中没有数字，则使用String。
2)提取从1st位置到数字开始位置的字母。
3)从开始位置提取数字直到结束。如果数字不存在，则设置为NULL

--Dataset Preparation
    with test (col) as
      (select 'ABC1234' from dual union all
       select 'ABC'     from dual union all
       select 'dEfH456'  from dual union all
       select '123GHI'  from dual union all
       select '456'     from dual
      )
     --Query
    select col Original_Column, 
           CASE 
              WHEN (instr(regexp_replace(col,'[0-9]','1'),'1',1)) = 0
           then col
           else
           substr( col,1,instr(regexp_replace(col,'[0-9]','1'),'1',1)-1) 
           end Col_Alp,

           CASE 
              WHEN (instr(regexp_replace(col,'[0-9]','1'),'1',1)) = 0
            then NULL
            Else
            substr( col,instr(regexp_replace(col,'[0-9]','1'),'1',1)) 
            END col_digit    
   from test
   where regexp_like(col, '^[a-zA-Z0-9]+$');

结果：

SQL> /
Original_Column Col_Alp col_digit
----------      -----   -----
ABC1234      ABC       1234
ABC          ABC       NULL
dEfH456      dEfH      456
123GHI       NULL       123GHI
456          NULL       456

使用SUBSTR（以及INSTR和TRANSLATE）：
SQL小提琴

Oracle 11g R2模式设置：

CREATE TABLE data ( value ) AS
SELECT 'ABC1234'     FROM DUAL UNION ALL
SELECT 'ABC123D'     FROM DUAL UNION ALL
SELECT 'ABC '        FROM DUAL UNION ALL
SELECT 'ABC'         FROM DUAL UNION ALL
SELECT 'DEFG456'     FROM DUAL UNION ALL
SELECT 'GHI'         FROM DUAL UNION ALL
SELECT 'JKLMNOPQRS9' FROM DUAL;

查询1：

SELECT value,
       SUBSTR( value, 1, first_digit - 1 ) AS prefix,
       TO_NUMBER( SUBSTR( value, first_digit ) ) AS suffix
FROM   (
  SELECT value,
         INSTR(
           TRANSLATE( value, '-1234567890', ' ----------' ),
           '-',
           1
         ) AS first_digit
  FROM   data
)
WHERE  SUBSTR( value, first_digit ) IS NOT NULL
AND    TRANSLATE( SUBSTR( value, first_digit ), '-1234567890', ' ' ) IS NULL

结果：

|       VALUE |     PREFIX | SUFFIX |
|-------------|------------|--------|
|     ABC1234 |        ABC |   1234 |
|     DEFG456 |       DEFG |    456 |
| JKLMNOPQRS9 | JKLMNOPQRS |      9 |

尝试下面的查询场景提到，我没有分裂，如果字符后面跟着数字：

with test (col) as
  (select 'ABC1234' from dual union all
   select 'ABC'     from dual union all
   select 'dEfH456'  from dual union all
   select '123GHI'  from dual union all
   select '456'     from dual
  )

  select col,reverse(trim(regexp_replace(reverse(col),'^[0-9]+',' '))) string ,trim(regexp_replace(col,'^[a-zA-Z]+',' ')) numbers from test

如果要将字符串移动到任意位置，请使用case语句

with test (col) as
  (select 'ABC1234' from dual union all
   select 'ABC'     from dual union all
   select 'dEfH456'  from dual union all
   select '123GHI'  from dual union all
   select '456'     from dual
  )

  select v.col,case when v.string=v.numbers THEN NULL ELSE string end string , v.numbers
  from (select col,reverse(trim(regexp_replace(reverse(col),'^[0-9]+',' '))) string ,trim(regexp_replace(col,'^[a-zA-Z]+',' ')) numbers from test) v

像这样的东西可以吗？

SQL> with test (col) as
  2    (select '"ABC1234", "ABC", "dEf456", "123GHI", "456"' from dual),
  3  inter as
  4    (select trim(regexp_substr(replace(col, '"', ''), '[^,]+', 1, level)) token
  5     from test
  6     connect by level <= regexp_count(col, ',') + 1
  7    )
  8  select regexp_substr(token, '^[a-zA-Z]+') letters,
  9         regexp_substr(token, '[0-9]+$') digits
 10  from inter
 11  where regexp_like(token, '^[a-zA-Z]+[0-9]+$');

LETTERS    DIGITS
---------- ----------
ABC        1234
dEf        456

SQL>