CODEWITHSUNDEEP
sqloracle-database
Walter41
Walter41

Reputation: 3

Oracle SQL: Using CASE in a SELECT statement with Substr

In a SELECT statement would it be possible to evaluate a Substr using CASE? Or what would be the best way to return a sub string based on a condition?

I am trying to retrieve a name from an event description column of a table. The string in the event description column is formatted either like text text (Mike Smith) text text or text text (Joe Schmit (Manager)) text text. I would like to return the name only, but having some of the names followed by (Manager) is throwing off my SELECT statement.

This is my SELECT statement:

 SELECT *
 FROM (
 SELECT Substr(Substr(eventdes,Instr(eventdes,'(')+1),1,
    Instr(eventdes,')') - Instr(eventdes,'(')-1)
   FROM mytable
      WHERE admintype = 'admin'
    AND entrytime BETWEEN sysdate - (5/(24*60)) AND sysdate
    AND eventdes LIKE '%action taken by%'
     ORDER BY id DESC
     )
 WHERE ROWNUM <=1

This returns things like Mike Smith if there is no (Manager), but returns things like Joe Schmit (Manager if there is.

Any help would be greatly appreciated.

Upvotes: 0

Views: 1543

Answers (4)

MT0
MT0

Reputation: 167867

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE MYTABLE ( id, admintype, entrytime, eventdes ) AS
SELECT 1, 'admin', SYSDATE, 'action taken by (John Doe (Manager)) more text' FROM DUAL;

Query 1:

SELECT *
FROM ( SELECT REGEXP_SUBSTR( eventdes, '\((.*?)(\s*\(.*?\))?\)', 1, 1, 'i', 1 )
       FROM   mytable
       WHERE  admintype = 'admin'
       AND    entrytime BETWEEN sysdate - (5/(24*60)) AND sysdate
       AND    eventdes LIKE '%action taken by%'
       ORDER BY id DESC
     )
WHERE ROWNUM <=1

Results:

| REGEXP_SUBSTR(EVENTDES,'\((.*?)(\S*\(.*?\))?\)',1,1,'I',1) |
|------------------------------------------------------------|
|                                                   John Doe |

Edit:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE MYTABLE ( id, admintype, entrytime, eventdes ) AS
SELECT 1, 'admin', SYSDATE, 'action taken by (Doe, John (Manager)) more text' FROM DUAL;

Query 1:

SELECT SUBSTR( Name, INSTR( Name, ',' ) + 1 ) || ' ' || SUBSTR( Name, 1, INSTR( Name, ',' ) - 1 ) AS Full_Name,
       REGEXP_REPLACE( Name, '^(.*?),\s*(.*)$', '\2 \1' ) AS Full_Name2
FROM ( SELECT REGEXP_SUBSTR( eventdes, '\((.*?)(\s*\(.*?\))?\)', 1, 1, 'i', 1 ) AS Name
       FROM   mytable
       WHERE  admintype = 'admin'
--       AND    entrytime BETWEEN sysdate - (5/(24*60)) AND sysdate
       AND    eventdes LIKE '%action taken by%'
       ORDER BY id DESC
     )
WHERE ROWNUM <=1

Results:

| FULL_NAME | FULL_NAME2 |
|-----------|------------|
|  John Doe |   John Doe |

Upvotes: 3

Gary_W
Gary_W

Reputation: 10360

Here's a slightly different take on the problem. This regexp starts at the position of the first open paren + 1 (the first letter of the name), then returns the first group from that point in the string on that consists of 1 or more characters that is not a close paren or an open paren. The TRIM gets rid of the trailing space if there is a manager.

SQL> with tbl(id, admintype, entrytime, eventdes ) AS
  2  ( SELECT 1, 'admin', SYSDATE, 'action taken by (John Doe (Manager)) more text' FROM DUAL
  3    union
  4    SELECT 1, 'admin', SYSDATE, 'action taken by (Jane Doe) more text' FROM DUAL
  5  )
  6  select trim(regexp_substr(eventdes, '([^\)\(]+)', instr(eventdes, '(')+1, 1)) name
  7  from tbl;

NAME
----------------------------------------------
Jane Doe
John Doe

SQL>

Upvotes: 0

jac
jac

Reputation: 9726

There is probably a regex that would do this without a replace but I am not smart enough to work it out so I combined a REGEXP_SUBSTRING that replaces your multiple instr methods and a REGEXP_REPLACE to replace the ' (MANAGER)' portion is it exists.

SELECT *
FROM (
    SELECT REGEXP_REPLACE(
        REGEXP_SUBSTR(eventdes, '\((.*)\)', 1, 1, 'i', 1), ' \(MANAGER\)', '')
    FROM mytable
    WHERE admintype = 'admin'
    AND entrytime BETWEEN sysdate - (5/(24*60)) AND sysdate
    AND eventdes LIKE '%action taken by%'
    ORDER BY id DESC
 )
WHERE ROWNUM <=1

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

You could use INSTR to search the last ')', but I would prefer a Regular Expression.

This extracts everything between the first '(' and the last ')' and the TRIMs remove the brackets (Oracle doesn't support look around in RegEx):

RTRIM(LTRIM(REGEXP_SUBSTR(eventdes, '\(.*\)'), '('), ')')

Upvotes: 1

Related Questions