CODEWITHSUNDEEP
sqloracle-databasesubstring
Lorik Berisha
Lorik Berisha

Reputation: 263

Substring Regular Expression for occurence

I want to select part of the string which occurs after the first underline _ and before the second, third or whatever amount of underlines _ occur in a string.

For example I have strings such as:

75618_LORIK1_2_BABA_ODD_GENERIC
19_GENTRIT3_CC_DD_FF_BROWSERTC
75618_BETIM2

Output should be:

LORIK1
GENTRIT3
BETIM2

I cant seem to find some kind of expression of substring to get that part, I tried using: 

SELECT SUBSTR(COLNAME, 0, INSTR(COLNAME, '_')-1) FROM DUAL;

But it seems to get only the part before the first occurrence of '_'.

Upvotes: 0

Views: 62

Answers (1)

user5683823
user5683823

Reputation:

Here's one way to do this with regular expressions.

with
  test_data (str) as (
    select '75618_LORIK1_2_BABA_ODD_GENERIC' from dual union all
    select '19_GENTRIT3_CC_DD_FF_BROWSERTC'  from dual union all
    select '75618_BETIM2'                    from dual union all
    select 'NO UNDERLINES HERE'              from dual
  )
select str, regexp_substr(str, '[^_]*', 1, 3) as second_token
from   test_data
;

STR                             SECOND_TOKEN                   
------------------------------- -------------------------------
75618_LORIK1_2_BABA_ODD_GENERIC LORIK1                         
19_GENTRIT3_CC_DD_FF_BROWSERTC  GENTRIT3                       
75618_BETIM2                    BETIM2                         
NO UNDERLINES HERE

Upvotes: 3

Related Questions