Oracle sql : get only specific part of a substring

Question

I'm struggling with a query in Oracle SQL, wanting to get some timings out of some text stored in an Oracle db.

Table :
kde_test (myString varchar(50))

Table contents (3 records):  
'task1 - 6m'
'task2 - 66m' 
'task3 - 666m'

I would like to get only the interesting part of the string, being the timings, so I would like to get only '6', '66' & '666' as results.

Searched this forum a bit, and got up with this query eventually, but it seems I do not completely get it, as the results it returns are : 6m 66m 666m

select
CASE
WHEN myString like 'task1%' THEN substr(myString,9,INSTR(myString,'m',1,1)-1)
WHEN myString like 'task2%' THEN substr(myString,9,INSTR(myString,'m',1,1)-1)
WHEN myString like 'task3%' THEN substr(myString,9,INSTR(myString,'m',1,1)-1)
END
from kde_test
where myString like 'task%'

EDIT :

Since some solutions (thanks already for quick response) take into account the specific values (eg. all 3 records ending on '6m'), maybe it best to take into account the values could be :

Table contents (3 records):  
'task1 - 6m'
'task2 - 58m' 
'task3 - 123m'

Answer 1

you can use this way too

    select regexp_replace(SUBSTR('task3 - 666m' ,
    INSTR('task3 - 666m', '-',1, 1)+1, length('task3 - 666m')), '[A-Za-z]') 
   from  dual


result :666

Answer 2

Use SUBSTR and INSTR and make it dynamic.

SUBSTR(str, 
       instr(str, ' - ', 1, 1) +3, 
       instr(str, 'm', 1, 1) - 
       instr(str, ' - ', 1, 1) -3
      )

For example,

SQL> WITH DATA AS(
  2  SELECT 'task1 - 6m' str FROM dual UNION ALL
  3  SELECT 'task2 - 66m' str FROM dual UNION ALL
  4  SELECT 'task3 - 666m' str FROM dual UNION ALL
  5  SELECT 'task4 - 58m' str FROM dual UNION ALL
  6  SELECT 'task5 - 123m' str FROM dual
  7  )
  8  SELECT str,
  9    SUBSTR(str, instr(str, ' - ', 1, 1) +3,
 10                instr(str, 'm', 1, 1) - instr(str, ' - ', 1, 1) -3) new_st
 11  FROM DATA;

STR          NEW_STR
------------ ------------
task1 - 6m   6
task2 - 66m  66
task3 - 666m 666
task4 - 58m  58
task5 - 123m 123

SQL>

Answer 3

You can use the regex_substr function. \d+ means one or more digits, and $ anchors the end of the string.

select  regexp_substr(str, '\d+m$')
from    mytable

Example at SQL Fiddle.