How to check instring for alphanumeric values

I have the following dataset: 

## IDENTIFIER | Column1        | column 2 ##          
IDENT1        | 150, oven, 450 | 159,246,849

I need to check whether or not one of the comma separated values is an alphanumeric value. How can I do this?

Answer

This could be one option; read comments within code.

SQL> with
  2  test (id, col1, col2) as
  3    -- sample data
  4    (select 1, '150,oven,450', '159,246,849' from dual union all  --> valid
  5     select 2, 'abc,123'     , 'def,1#5,331' from dual            --> col2 invalid
  6    ),
  7  temp as
  8    -- split comma-separated values into rows, check every part of it. If it is not alphanumeric, return 1
  9    (select id,
 10      col1,
 11      col2,
 12      case when not regexp_like(regexp_substr(col1, '[^,]+', 1, a.column_value), '^[[:alnum:]]+$') then 1
 13           else 0
 14      end val1,
 15      --
 16      case when not regexp_like(regexp_substr(col2, '[^,]+', 1, b.column_value), '^[[:alnum:]]+$') then 1
 17           else 0
 18      end val2
 19     from test cross join
 20       table(cast(multiset(select level from dual
 21                           connect by level <= regexp_count(col1, ',') + 1
 22                          ) as sys.odcinumberlist)) a
 23            cross join
 24       table(cast(multiset(select level from dual
 25                           connect by level <= regexp_count(col2, ',') + 1
 26                          ) as sys.odcinumberlist)) b
 27    )
 28  -- final query; if all parts of the column are alphanumerics, summary is 0 so it is valid.
 29  -- Otherwise, summary is larger than 0 and is invalid
 30  select id,
 31    col1,
 32    col2,
 33    case when sum(val1) > 0 then 'invalid' else 'valid' end valid_col1,
 34    case when sum(val2) > 0 then 'invalid' else 'valid' end valid_col2
 35  from temp
 36  group by id, col1, col2
 37  order by id;

        ID COL1         COL2        VALID_COL1 VALID_COL2
---------- ------------ ----------- ---------- ----------
         1 150,oven,450 159,246,849 valid      valid
         2 abc,123      def,1#5,331 valid      invalid

SQL>