Dear Experts,
I populated a table with few rows of strings that are Upper/ Lower/ Mixed case, alpha-numeric, numeric etc.
1. Now I would like to evaluate they type of string using a case statement. I tried using regexp_like, but it fails when I use [[:alphanum:]] in the first line of case statement. It evaluate everything to alpha-numeric regardless of the string type. However when I use at the last and final line it works. Any thoughts why?
2. Using the same case statements, I also need to find if a string is mixed case, for example 'Az' but it is being evaluated as 'Alpha Numeric'. Please advise if I am missing anything.
Desired Output
CODE DAT_TYPE
A1 Alpha Numeric
A2 Alpha Numeric
Az Mixed Case
AK Upper
10 Numeric
oh lower
Please find the code for creating, populating and querying the table with regexp_like. You may also find it on livesql (
https://livesql.oracle.com/apex/livesql/s/heydibqsfr4unh979rs4cv42r )
drop table q1 purge;
create table q1
(code varchar2(20)
);
insert into q1 values ('A1');
insert into q1 values ('A2');
insert into q1 values ('Az');
insert into q1 values ('AK');
insert into q1 values (10);
insert into q1 values ('oh');
commit;
/* QUERY 1
Aphanumeric evaluation in the first line of case statement
The result is all rows evaluate to 'Alpha Numeric' regardless of their types */
select code,
case
when regexp_like (code, '^[[:alnum:]]+$') then 'Alpha Numeric'
when regexp_like (code, '^[[:upper:]]+$') then 'Upper'
when regexp_like (code, '^[[:digit:]]+$') then 'Numeric'
when regexp_like (code, '^[[:lower:]]+$') then 'lower'
end dat_type
from q1;
/* Output of QUERY 1
CODE DAT_TYPE
A1 Alpha Numeric
A2 Alpha Numeric
Az Alpha Numeric
AK Alpha Numeric
10 Alpha Numeric
oh Alpha Numeric
*/
/* QUERY 2
Aphanumeric evaluation in the second line of the case statement
The result is only Upper case is evaluated correctly and the rest of the
rows evaluate to 'Alpha Numeric' regardless of their types */
select code,
case
when regexp_like (code, '^[[:upper:]]+$') then 'Upper'
when regexp_like (code, '^[[:alnum:]]+$') then 'Alpha Numeric'
when regexp_like (code, '^[[:digit:]]+$') then 'Numeric'
when regexp_like (code, '^[[:lower:]]+$') then 'lower'
end dat_type
from q1;
/* Output of QUERY 2
CODE DAT_TYPE
A1 Alpha Numeric
A2 Alpha Numeric
Az Alpha Numeric
AK Upper
10 Alpha Numeric
oh Alpha Numeric
*/
/* QUERY 3
Aphanumeric evaluation in the third line of the case statement
The result is only Upper case and numeric are evaluated correctly and the rest of the
rows evaluate to 'Alpha Numeric' regardless of their types */
select code,
case
when regexp_like (code, '^[[:upper:]]+$') then 'Upper'
when regexp_like (code, '^[[:digit:]]+$') then 'Numeric'
when regexp_like (code, '^[[:alnum:]]+$') then 'Alpha Numeric'
when regexp_like (code, '^[[:lower:]]+$') then 'lower'
end dat_type
from q1;
/* Output of QUERY 3
CODE DAT_TYPE
A1 Alpha Numeric
A2 Alpha Numeric
Az Alpha Numeric
AK Upper
10 Numeric
oh Alpha Numeric
*/
/* QUERY 4
Aphanumeric evaluation in the last or final line of the case statement
All the rows are evaluated properly
*/
select code,
case
when regexp_like (code, '^[[:upper:]]+$') then 'Upper'
when regexp_like (code, '^[[:digit:]]+$') then 'Numeric'
when regexp_like (code, '^[[:lower:]]+$') then 'lower'
when regexp_like (code, '^[[:alnum:]]+$') then 'Alpha Numeric'
end dat_type
from q1;
The database processes case expressions top to bottom. It returns the value from the first clause that is true. Then skips the rest.
The test for [[:alnum:]] is true if the input contains any characters A-Z, a-z, and 0-9.
Is "10" in that list?
Yes!
So [[:alnum:]] matches this string. If you place the test for this first (as in your first query), it matches every string you have.
You first need to test for the classes which contain no other classes within them.
So run your tests in this order:
1. lowercase only, uppercase only, numbers only (it doesn't matter which order; there's no overlap between them)
2. Any alphabetic character ([[:alpha:]]) - this gives mixed case
3. Alphanumeric last
Like so:
select code,
case
when regexp_like (code, '^[[:upper:]]+$') then 'Upper'
when regexp_like (code, '^[[:lower:]]+$') then 'lower'
when regexp_like (code, '^[[:digit:]]+$') then 'Numeric'
when regexp_like (code, '^[[:alpha:]]+$') then 'MiXeD'
when regexp_like (code, '^[[:alnum:]]+$') then 'Alpha Numeric'
end dat_type
from q1;
CODE DAT_TYPE
A1 Alpha Numeric
A2 Alpha Numeric
Az MiXeD
AK Upper
10 Numeric
oh lower