Oracle SQL Regular Expression (regexp_substr)

Question

I have been researching and trying to use Regular Expressions in Oracle SQL to select a substring within a string. I only want to select “UT”, “T1”, or “T2” values and I want to select whichever one of these values occurs last in the string.

“INPUT” column shows my example data, “TARGET” column shows the value I want, “OUTPUT” shows the values I am getting with my current regular expression statement

(SELECT regexp_substr(INPUT, '_(UT|T[A-Z]*[1-2]*)', 1, 1, '', 1) FROM table)

(as a note, I have tried changing the starting index position to -1 in my statement above but it is not supported)

Thank you

INPUT

XXs5_ABC_94_T2_99
ABs9_AXY_09_UT
LPs3_SHT9_01_T1_90
OOs7_POT_0_UT_T1_89
IPs0_XYS_18_UT_T1_19
VGs5_POT7_01_T1_15_T2_45

TARGET

T2
UT
T1
T1
T1
T2

OUTPUT

T2
UT
T1
UT
UT
T1

score 2 · Accepted Answer · 2018-08-23 18:20:22Z

Your query is almost correct already. Just add .* at the beginning of the pattern, to force the match of the alternation to be found as far as possible in the input string (while still allowing for a match of the entire pattern).

with
  table_ (input) as (
    select 'XXs5_ABC_94_T2_99'        from dual union all
    select 'ABs9_AXY_09_UT'           from dual union all
    select 'LPs3_SHT9_01_T1_90'       from dual union all
    select 'OOs7_POT_0_UT_T1_89'      from dual union all
    select 'IPs0_XYS_18_UT_T1_19'     from dual union all
    select 'VGs5_POT7_01_T1_15_T2_45' from dual
  )
select input, 
       regexp_substr(input, '.*_(UT|T[A-Z]*[1-2]*)', 1, 1, '', 1) as req_substr
from   table_
;

INPUT                    REQ_SUBSTR              
------------------------ ------------------------
XXs5_ABC_94_T2_99        T2                      
ABs9_AXY_09_UT           UT                      
LPs3_SHT9_01_T1_90       T1                      
OOs7_POT_0_UT_T1_89      T1                      
IPs0_XYS_18_UT_T1_19     T1                      
VGs5_POT7_01_T1_15_T2_45 T2

Collectives™ on Stack Overflow

Oracle SQL Regular Expression (regexp_substr)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related