SQL: Feeding SELECT output to LIKE

Question

Problem:

select STR1 from T1 where STR2 = 'NAME1'

In the above query STR1 can be in form {ABC, ABC_1, ABC_2,..., MNO, XYZ, XYZ_1...}.

So let suppose I have following output

ABC_1
MNO
XYZ

Now I want to extract all those matching STR1 that include the part before _#. For example the expected output for the example dataset above is:

ABC
ABC_1
ABC_2

MNO

XYZ
XYZ_1

Note that STR2 is always unique per STR1.

Code wise I imagine some thing like following:

SELECT 
    STR1 
FROM 
    T1 
WHERE
    STR1 
LIKE '% (truncate_underscore_part(select STR1 from T1 where STR2 = 'NAME1')) %'

Any idea?

First solution:

select t1.str1
  from (
  select case when instr( str1, '_' ) > 0
                then substr( str1, 1, instr( str1, '_' ) - 1 )
              else str1
         end prefix
    from t1 where str2 = 'NAME1'
) prefix_list,
  t1
  where t1.str1 like prefix || '%'

Answer 1

with prefix_list as (
  select regexp_substr( str1, '^[A-Z]*' ) prefix from t1 where str2 = 'NAME1'
)
select t1.str1 from t1 join prefix_list
        on t1.str1 = prefix_list.prefix
           or regexp_like( t1.str1, prefix_list.prefix||'_[0-9]' )

To do it without the regexp functions (for older Oracle versions), it depends a bit on how much you want to validate the format of the strings.

select t1.str1
  from (
  select case when instr( str1, '_' ) > 0
                then substr( str1, 1, instr( str1, '_' ) - 1 )
              else str1
         end prefix
    from t1 where str2 = 'NAME1'
) prefix_list,
  t1
where t1.str1 = prefix
   or t2.str1 like prefix || '\__' escape '\'