CODEWITHSUNDEEP
sqldatabaseoracle-database
allencharp
allencharp

Reputation: 1161

How to use substr in Oracle ?

there is a table column message value is 

Command triggerEvent started
Command stopService stopped
Command startService started
Command executeCommand running
......

Now I want to select the command name, i.e. triggerEvent, stopService, startService

I try to use oracle substr and instr to get them but failed...

select substr(message, instr(message, ' ')) from event

Is there any good way to do that ?

Upvotes: 2

Views: 358

Answers (3)

Tim Chaubet
Tim Chaubet

Reputation: 726

Make sure u see the distinction between lobs and strings for substr !

select 
   substr(test, 4),
   dbms_lob.substr(test, 4)
from (
   select ('1234567890') test from dual
);

results in

enter image description here

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You could do it with shorter code using REGEXP_SUBSTR.

For example,

SQL> WITH sample_data AS(
  2  SELECT 'Command triggerEvent started' str FROM dual UNION ALL
  3  SELECT 'Command stopService stopped' str FROM dual UNION ALL
  4  SELECT 'Command startService started' str FROM dual UNION ALL
  5  SELECT 'Command executeCommand running' str FROM dual
  6  )
  7  -- end of sample_data mocking as real table
  8  SELECT trim(regexp_substr(str, '[^ ]+', 1, 2)) command
  9  FROM sample_data;

COMMAND
------------------------------
triggerEvent
stopService
startService
executeCommand

Of course, better to use SUBSTR and INSTR as they are less CPU intensive as still faster than REGEX.

SQL> WITH sample_data AS(
  2  SELECT 'Command triggerEvent started' str FROM dual UNION ALL
  3  SELECT 'Command stopService stopped' str FROM dual UNION ALL
  4  SELECT 'Command startService started' str FROM dual UNION ALL
  5  SELECT 'Command executeCommand running' str FROM dual
  6  )
  7  -- end of sample_data mocking as real table
  8  SELECT trim(SUBSTR(str, instr(str, ' ', 1, 1),
  9                     instr(str, ' ', 1, 2) - instr(str, ' ', 1, 1))
 10              ) command
 11  FROM sample_data;

COMMAND
------------------------------
triggerEvent
stopService
startService
executeCommand

Upvotes: 2

Utsav
Utsav

Reputation: 8093

This will give you the second word from your message

with event(message) as (
select 'command triggerEvent started' from dual union
select 'Command stopService stopped' from dual) --recreate table

--query start
select trim(regexp_substr(message,'[[:alpha:]]+ ',1,2)) command FROM event

Upvotes: 0

Related Questions