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?
Advertisement
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>