I am using regexp_like to flag rows as 'Y' if they have any of the search expressions else 'N'. However, I want to constraint the search lines that are not part of a single line comment (--) or multi-line comment (/* ... */)
The expected result for the query below when searching for any of these htm, ui, or sec
1 N
2 Y
3 N
with
src as (
select 1 as rn, '/****
htm.opn
****/
This is a line' as str
from dual
union all
select 2 as rn, '/* ui.close */ This has ui.open' from dual
union all
select 3 as rn, '-- sec.drv
' from dual
)
select rn
, case when not regexp_like(str,'(--)|(/*.**/)')
and regexp_like(str,'htm\.|ui\.|sec\.')
then 'Y' else 'N'
end as flag
, str
from src
where 1=1
/