Ηι,
let's suppose that there are the following rows:
select 'RE89203' a from dual union all
select 'cd89203' a from dual union all
select 'cD89203' a from dual union all
select 'CZC4255463' a from dual union all
select '597CD83843KL' a from dual union all
select '597CD90832 83843FE' a from dual union all
select '597CD90832~83843FE' a from dual union all
select 'CD9083283843FΔ' a from dual union all
select 'CD9083283843FΑ' a from dual
In the above list, i want to get the rows containg at least one digit and at least one english character (in capital) but except for these any other character is not acceptable (and the row as well).
I tried the following but the result set is not the desired:
with sample_data as
(
select 'RE89203' a from dual union all
select 'cd89203' a from dual union all
select 'cD89203' a from dual union all
select 'CZC4255463' a from dual union all
select '597CD83843KL' a from dual union all
select '597CD90832 83843FE' a from dual union all
select '597CD90832~83843FE' a from dual union all
select 'CD9083283843FΔ' a from dual union all
select 'CD9083283843FΑ' a from dual
)
select a
from sample_data
where regexp_like(a, '[A-Z][[:digit:]]','c')
Explanations:
The record value 'cD89203' is not acceptable because it contains the character 'c' (in lower case).
The record value '597CD90832 83843FE' is not acceptable because there is a space.
The record value '597CD90832~83843FE' is not acceptable because there is a character '~'.
The record value 'CD9083283843FΔ' is not acceptable because there is a non english caracter (character 'Δ').
The record value of the last record 'CD9083283843FΑ' is not acceptable because there is a non english caracter (character 'Α').
Note:
I use OraDB 11g v2