CODEWITHSUNDEEP
oracle-databaseplsqlcharacter
Dereck
Dereck

Reputation: 893

Oracle Query to select only the text between two '-' characters

I would like to replace a column values selecting only the text inside the two '-' character. Example: Case 1 - Deleted - The case is closed.

So, i would like something like this:

Original:

Description
------------------------------------------
Case 1 - Deleted - The case is closed

What I want to have:

Description
------------------------------------------
Deleted

I have this but i dont know how to delete the text after the second '-' and replace that result on the current column Description

select SUBSTR(description, INSTR(description, '-') + 1) left_value
from (
    select description
    from all_cases
    );

Upvotes: 0

Views: 12307

Answers (4)

Mark J. Bobak
Mark J. Bobak

Reputation: 14395

I think you're looking for something like this:

SQL>  variable s1 varchar2(40)
SQL> exec :s1:='Case 1 - Deleted1 - The case is closed';

PL/SQL procedure successfully completed.

SQL> select substr(:s1,instr(:s1,'-')+1,instr(:s1,'-',2)+1) substr_value from dual;

SUBSTR_VALUE
------------------------------
 Deleted1

Upvotes: 0

Gary_W
Gary_W

Reputation: 10360

Avoid all that fugly nested substr/instr with REGEXP_SUBSTR():

SQL> with tbl(str) as (
     select 'Case 1 - Deleted - The case is closed' from dual
   )
   select trim(regexp_substr(str, '-(.*)-', 1, 1, null, 1)) fixed
   from tbl;

FIXED
-------
Deleted

Edit: Changed to simplify regex and allow for cases where there may not be spaces inside of the dashes.

Upvotes: -1

Jair Hernandez
Jair Hernandez

Reputation: 494

Since INSTR function allows to set what occurrence of any character you want, you can use this to identify the pieces to erase based on the "-" character:

select  trim(
            replace(  
                    replace(
                                description, 
                                SUBSTR(description, 1, INSTR(description, '-',1,1)), -- <-- first occurrence of "-"
                                null), 
                    SUBSTR(description, INSTR(description, '-',1,2), -- <-- second occurrence of "-"
                    length(description)), 
                    null)
        ) as result
from all_cases;

Assuming that all your rows follow the same pattern, update all in one call would look something like this:

update all_cases
set description = trim(
                    replace(  
                        replace(description, 
                                SUBSTR(description, 1, INSTR(description, '-',1,1)),
                                null), 
                        SUBSTR(description, INSTR(description, '-',1,2), length(description)), 
                        null)
                    );

Which acts just identifying the left and right pieces based in the "-" character and replacing them with null.

Upvotes: 1

Oto Shavadze
Oto Shavadze

Reputation: 42753

One possible way:

with t(col) as(
select 'Case # - Reason - Bla Bla' from dual union all
select 'Case # - Reason2 - Bla Bla' from dual 
)

select 
substr( 
    col, 
    INSTR (col , '-' , 1, 1)+1,  
    INSTR (col , '-' , 1, 2) - INSTR (col , '-' , 1, 1) - 1
)
from t

Upvotes: 1

Related Questions