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'

It looks like this can be simplified to finding the character string between the second occurrence of a space and the last but one, non-inclusive. — David Aldridge
– David Aldridge, Commented Oct 1, 2015 at 9:19
No problem, you could simply use SUBSTR and INSTR and make it dynamic. See the edited answer with your edited values. — Lalit Kumar B
– Lalit Kumar B, Commented Oct 1, 2015 at 16:35

Moudiz · Accepted Answer · 2015-10-01 09:30:42Z

2

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

answered Oct 1, 2015 at 9:30

Moudiz

7,42723 gold badges87 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lalit Kumar B · Accepted Answer · 2015-10-01 16:34:16Z

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>

edited Oct 1, 2015 at 16:34

answered Oct 1, 2015 at 9:24

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Comments

Andomar · Accepted Answer · 2015-10-01 09:24:39Z

1

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.

answered Oct 1, 2015 at 9:24

Andomar

239k55 gold badges387 silver badges412 bronze badges

Comments

Yaron Idan · Accepted Answer · 2015-10-01 09:47:17Z

1

In order to correct your current query, you should change the following string - "INSTR(myString,'m',1,1)-1" to "INSTR(myString,'m',1,1)-9". However, the other answers provided above seem like a more elegant solution to your problem.

I did feel the need to publish this just to clarify what wasn't working well in current query - in INSTR function returns the position of the m letter, and then used as the length of the string to print. What my fix does is telling the query to print everything from the 9th character until the position of the m letter, which results in the task time required.

answered Oct 1, 2015 at 9:47

Yaron Idan

6,7957 gold badges47 silver badges72 bronze badges

Comments

Gaurav Soni · Accepted Answer · 2015-10-01 10:57:55Z

1

I have tried to divide this into two parts

First pick the string after -

  regexp_substr ('task1 - 1234m', '[^ _ ]+',1, 3) --results 1234m

Fetch the number part of the string fetched from output of first

 regexp_substr(regexp_substr ('task1 - 1234m', '[^ _ ]+',1, 3),'[[:digit:]]*') 
 --output 1234

So,the final query is

 SELECT regexp_substr(regexp_substr (mystring, '[^ _ ]+',1, 3),'[[:digit:]]*')
 FROM kde_test;

answered Oct 1, 2015 at 10:57

Gaurav Soni

6,37610 gold badges55 silver badges72 bronze badges

Comments

Praveen · Accepted Answer · 2015-10-01 11:43:46Z

1

Use This:-

select substr(replace(myString,'m',''),9) output
from kde_test
where myString like 'task%'

edited Oct 1, 2015 at 11:43

Praveen

9,4634 gold badges38 silver badges51 bronze badges

answered Oct 1, 2015 at 11:36

jitendra joshi

6971 gold badge5 silver badges18 bronze badges

Collectives™ on Stack Overflow

Oracle sql : get only specific part of a substring

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related