CODEWITHSUNDEEP
sqlregexoracle-database
Rajiv A
Rajiv A

Reputation: 943

Regexp_substr to extract after certain set of characters Oracle

Hello I want only 1 expression to fulfill all the 3 below criteria.String will will always start with CALL or (

Will Replace work better or Substr

1) pick 4 characters after )

2) pick 4 characters after RX:

3) pick 4 characters before PASS For example : 

CALL RX: W234    =>W234
(S123) T123 #100 =>T123
CALL Y123 PASS   =>Y123
CALL Q12  PASS   =>Q12

I tried I tried Replace REGEXP_REPLACE(text,'CALL (RX:)?|PASS|\(.*\)|[[:punct:]].*'). But it is not extracting LXC2 for this string (D123) LXC2 99

Upvotes: 0

Views: 236

Answers (2)

Gary_W
Gary_W

Reputation: 10360

Here's a one-liner. The Common Table Expression (the with clause) just sets up the data set for example's sake:

with tbl(idx, data) as (
  select 1, 'CALL RX: W234' from dual union all
  select 2, '(S123) T123 #100' from dual union all
  select 3, 'CALL Y123 PASS' from dual union all
  select 4, 'CALL Q12  PASS' from dual union all
  select 5, '(D123) LXC2 99' from dual
) 
select idx, 
  regexp_substr(data, '^(CALL|\(.*?\)) (RX: )?(.*?)( |PASS|$)', 1, 1, null, 3) result
from tbl;


       IDX RESULT          
---------- ----------------
         1 W234            
         2 T123            
         3 Y123            
         4 Q12             
         5 LXC2            

5 rows selected.

It uses groups to match lines starting with 'CALL' or a set of any characters inside of parenthesis followed by a space, then an optional string of 'RX:'. The next set of characters that are followed by a group of either a space, string of 'PASS' or the end of the line are captured non-greedily. This happens to be the third group, which is returned by the 6th argument.

Note this works with the sample data set you provided. If you have different examples, you may need to do some tweaking of the regex.

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

I think you're looking for extracting the string between two spaces. So consider using [[:space:]] POSIX regular expression with regexp_substr(), and regexp_count() and replace() as auxiliary :

with t(text) as
(                  
 select '(D123) LXC2 99'   from dual union all
 select 'CALL RX: W234'    from dual union all
 select '(S123) T123 #100' from dual union all
 select 'CALL Y123 PASS'   from dual 
), t2(text) as
(
 select replace(text,'CALL RX:','') from t
)
 select case when regexp_count(text,'[[:space:]]') >= 2 then 
             regexp_substr( text,'[[:space:]](.*)[[:space:]]') 
        else
             text
        end as "Result"
   from t2;

Result
------
LXC2
W234  
T123
Y123

Demo

Upvotes: 1

Related Questions