CODEWITHSUNDEEP
sqloracle-databasesubstring
pulga_gaucho
pulga_gaucho

Reputation: 71

Oracle sql : get only specific part of a substring

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'

Upvotes: 6

Views: 3555

Answers (6)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

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>

Upvotes: 2

jitendra joshi
jitendra joshi

Reputation: 687

Use This:-

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

Upvotes: 1

Gaurav Soni
Gaurav Soni

Reputation: 6346

I have tried to divide this into two parts

  1. First pick the string after - 

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

  2. 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;

Upvotes: 1

Yaron Idan
Yaron Idan

Reputation: 6765

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.

Upvotes: 1

Moudiz
Moudiz

Reputation: 7377

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

Upvotes: 2

Andomar
Andomar

Reputation: 238286

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.

Upvotes: 1

Related Questions