Return value starting with alphabets

Question

How to find out values starting with alphabets.

E.g.

I have data

prodCode

12FDBC
34IO
ZklM
hOP
12-b.9     

From the above I would like to have ZklM and hOP returned. How can I do this?

Answer 1

The obvious answer is upgrade if at all possible. I can't even find the date that extended support ended for 8i; for 9i it was 2007-07-31, almost 8 years ago.

Failing that, you're simply going to have to enumerate all the characters you want to accept; either something like this, which is overly long:

select *
  from ...
 where lower(substr(prod_code, 1, 1)) in ('a', 'b', ..., 'z')

Or this, which is less obvious but smaller:

select *
  from ...
 where trim(translate(lower(substr(prod_code, 1, 1))
                      , 'abcdefghijklmnopqrstuvwxyz', ' ')) is null

Alternatively, you could create a table with the characters you want to accept and join:

select *
  from ... a
     , alphabet b
 where substr(a.prod_code, 1, 1) = b.character

Lastly, you could use the ASCII() function, which I'm fairly sure existed in 8i

select *
  from ...
 where ascii(lower(substr(a.prod_code, 1, 1))) between 97 and 122

I explicitly lower case in most of these situations to make life easier; if you want this indexed you're going to have a functional index anyway and an additional LOWER() isn't going to make much difference.