SQL: feeding SELECT output to LIKE

Question

Problem:

select STR1 from T1 where STR2 = 'NAME1' 

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

So let's say I have the following conclusion

 ABC_1 MNO XYZ 

Now I want to extract all the corresponding STR1 that include the part up to _ # . For example, the expected result for the above example dataset:

 ABC ABC_1 ABC_2 MNO XYZ XYZ_1 

Note that STR2 is always unique to STR1.

The code is wise. I imagine something like the following:

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

Any idea?

First decision:

 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, '^[AZ]*' ) 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 this without the regexp functions (for older versions of Oracle), it depends a bit on how much you want to check 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 '\' 

Answer 2

One way to do this is to self-connect.

Here is an example for MySQL:

 SELECT t2.str1 FROM t1 INNER JOIN T1 as t2 on (t2.str1 = t1.str1 OR t2.str1 LIKE CONCAT(t1.str1,'\_%')) WHERE t1.STR2 = 'NAME1' 

And here's an attempt to translate this into Oracle syntax:

 SELECT t2.str1 FROM t1 INNER JOIN T1 as t2 on (t2.str1 = t1.str1 OR t2.str1 LIKE t1.str1 || '\_%') WHERE t1.STR2 = 'NAME1' 

Answer 3

In Oracle, I would use

 where regexp_like(str1, '[AZ]{3}(_[0-9]+)?')