CODEWITHSUNDEEP
sqloracle-database
Kaushal Talniya
Kaushal Talniya

Reputation: 192

Fetch data only if field contains any lower case in the string

I was trying to fetch data with condition "get data only if string contains the lower char in the 4th and 5th position". Below are the conditions:

and ascii(substr(RAP01.CRDVER,4,1)) between 97 and 122
and ascii(substr(RAP01.CRDVER,5,1)) between 97 and 122;

Table name RAP01 and column name CRDVER.

But It is not fetching all the required data. Can this approach is correct?

Upvotes: 1

Views: 199

Answers (2)

dani herrera
dani herrera

Reputation: 51645

  • Are lower case a subset of ASCII? NO
  • Can I use ASCII to identify lower case? NO

How can I know if a string has lower case? It has lower case if UPPER of string is not the same as just the string.

Sample:

create table t1 ( a varchar(100) )\\

insert into t1 (a) values ( 'A' )\\
insert into t1 (a) values ( 'a' )\\
insert into t1 (a) values ( 'å' )\\
insert into t1 (a) values ( 'æ' )\\

select (case when a = upper( a )                  <-- compare with upper
        then a || ' Is Upper' 
        else a || ' Has lower' end) as r_upper,

       (case when not regexp_like(a, '[a-z]', 'c') <-- using ascii
        then a || ' Is Upper' 
        else a || ' Has lower' end) as r_reg

       from t1\\

R_UPPER     | R_REG 
-------------------------
A Is Upper  | A Is Upper
a Has lower | a Has lower
å Has lower | å Is Upper (¹)
æ Has lower | æ Is Upper (¹)

(¹) Wrong result using ASCII.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142710

How about this? The key is line 8 - query will return strings whose 4th and 5th character (substr(col, 4, 2))is a lowercase letter ([a-z]) (c means that the search is case sensitive).

SQL> with test (col) as
  2    (select '12Bcfx23' from dual union all
  3     select '123456'   from dual union all
  4     select 'ABCDEFGH' from dual
  5    )
  6  select col, substr(col, 4, 2) sub
  7  from test
  8  where regexp_like(substr(col, 4, 2), '[a-z]', 'c');

COL      SU
-------- --
12Bcfx23 cf

SQL>

Upvotes: 2

Related Questions