阅读代码中的注解。

SQL> with test (col) as
  2    -- sample data
  3    (select 'My ADSL 2M(200) Volume 14Mbps/2M speed threshold 0M' from dual),
  4  spaces as
  5    -- find positions of all spaces within the sample string
  6    (select instr(col, ' ', 1, level) space_position
  7     from test
  8     connect by level <= regexp_count(col, ' ')
  9    ),
 10  speed as
 11    -- find position of the " speed" string
 12    (select col,
 13            instr(col, ' speed') speed_position
 14     from test
 15    ),
 16  downup as
 17    -- extract the whole "donwload/upload" string
 18    (select trim(substr(col, s.space_position, p.speed_position - s.space_position)) val
 19     from speed p cross join spaces s
 20     where s.space_position = (select max(s1.space_position)
 21                               from spaces s1
 22                               where s1.space_position < p.speed_position
 23                             )
 24    )
 25  -- final result
 26  select regexp_substr(val, '\d+', 1, 1) download_speed,
 27         regexp_substr(val, '\d+', 1, 2) upload_speed
 28  from downup;

DOWNLOAD_SPEED  UPLOAD_SPEED
--------------- ---------------
14              2

SQL>

你可以用 REGEXP_REPLACE() 包含模式的函数，其中一个是从字符串开始的 Volume ，另一个是斜线( / )假设在每个字符串值中都是唯一的：

SELECT REGEXP_REPLACE(str,'(.*Volume )(\d+)(.*)','\2') AS download_speed, 
       REGEXP_REPLACE(str,'(.*\/)(\d+)(.*)','\2') AS upload_speed
  FROM tab

演示

你可以用 regexp_substr 具体如下：

with d (str) as
(select 'My ADSL 2M(200) Volume 14Mbps/2M speed threshold 0M'
 from dual)
 -- your query starts from here.
select regexp_substr(spd, '[0-9]+',1,1) as download_speed,
regexp_substr(spd, '[0-9]+',1,2) as upload_speed
from
 (select regexp_substr(str, '[0-9]+M[a-z|A-Z]+/[0-9]+[a-z|A-Z]+') as spd
    from d)