Q:

Oracle SQL Regular Expression (regexp_substr)

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

A:

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  

