Oracle sql: get only a specific part of a substring

Question

I am struggling with a query in Oracle SQL, wanting to get some timings from some text stored in 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 line, being timings, so I would like to get the results only "6", "66" and "666".

I looked for this forum a bit, and in the end I got up with this question, but it seems that I do not fully understand it, since the results that it returns are as follows: 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 a quick answer) take into account specific values ​​(for example, all 3 entries ending in "6m"), it may be best to consider the values:

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

Answer 1

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 instance,

 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 2

you can also use this method

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

Answer 3

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

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

Example in SQL Fiddle.

Answer 4

To fix your current query, you should change the following line: "INSTR (myString, 'm', 1,1) -1" to "INSTR (myString, 'm', 1,1) -9". However, the other answers presented above seem to be a more elegant solution to your problem.

I really felt the need to publish this just to clarify that it doesn’t work well in the current request - in the INSTR function, the position of the letter m is returned, and then used as the length of the string to print. What my correction does is say the request to print everything from the 9th character to the position of the letter m, which leads to the required task time.

Answer 5

I tried to split it into two parts

• First select the line after -

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

• Extract the numeric part of the string taken from the output of the first

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

So the final request

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

Answer 6

Use this: -

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