Re: PL/SQL

In article <89ln3o$8gj3_at_ppd00021.deutschepost.de>,   "Birgit Kemnitz" <B.Kemnitz_at_deutschepost.de> wrote:
> Hello,
> we are working with a database in oracle 8i.
> In a field which is defined as varchar2 are values like '01004578' and
> values with an alphabetical
> sign in it like '0100K124'. Is there a way to select all fields which
 have
> an alphabetical sign in it?
>
>

select *
  from t
 where translate( upper(field), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', rpad( 'X', 26, 'X' ) ) like '%X%'
/

the translate will replace all A-Z's with X's, then find the fields with X's in them.

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.