在子查询中，可以使用regexp函数 regexp_replace() 以及 regexp_extract() 将值与后缀分开。然后，可以聚合并应用外部查询中的逻辑：

select 
    value,
    case 
        when min(suffix) = '_NEWFORMAT' and max(suffix) = '_OLDDAT' 
            then 'Contains both NEW and OLD'
        when max(suffix) = '_NEWFORMAT'
            then 'Missing OLD'
        else 'Missing NEW'
    end reason
from (
    select 
        regexp_replace(datalist, '_OLDDAT|_NEWFORMAT$', '') value,
        regexp_extract(datalist, '(_OLDDAT|_NEWFORMAT)$', 1) suffix
    from mytable
) x
group by value