Comparing Substring Against Multiple Columns

Question

I have a table which has 20 similar text attribute columns, text1..text20. These columns are of type CLOB. I am looking to find rows where one of these text attribute columns contain a specific phrase, such as '%unemployed%'. I need to know 2 things, which rows match, and which column was matched on. I thought I could use ANY as a starting point, but I am having issues.

It appears the ANY statement does NOT work with '%'. For example,

select * from emp where 'BLAKE' = ANY(ename, job); -- Returns Data

but

select * from emp where '%BLAKE%' = ANY(ename, job) -- No Data Found

What would be the proper way to do this? Pseudo-code would be...

Select name, addr, 
which_column_matched(%unemployed%, text1..text20),
text1..text20
from table
where %unemployed% = ANY(text1..text20);

Answer 1

In Oracle, you can use unpivot for this. It still requires you to enumerate all the columns, but the syntax is quite neat.

If you want one record for each column that matches:

select *
from emp unpivot (col for src in (text1, text2, text3))
where col like '%unemployed%'

If you wanted one additional column with the list of matching columns instead, you can aggregate the resultset:

select ename, listagg(src, ', ')
from emp unpivot (col for src in (text1, text2, text3))
where col like '%unemployed%'
group by ename