Re: Identifying alpha characters only - help

rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0207311728.3500f13a_at_posting.google.com>...
> djw_123m_at_hotmail.com (Lakeuk) wrote in message news:<bcdae9a0.0207310209.3db4eab2_at_posting.google.com>...
> > I have a list of values in a column of which I only want my results
> > set to contain the records that match 3 alpha characters
> >
> > Value
> > -----
> > AAA
> > 001
> > 002
> > AB
> > A
> > BBB
> > BBB
> >
> > Using the data set above I want my query to only identify the
> > following records
> >
> > Result
> > ------
> > AAA
> > BBB
> > BBB
> >
> > Has anyone got any ideas of what SQL I need to write to get the
> > required result, I know I need to use the length function to match the
> > 3 characters but I don't know how to identify alpha character only
> > (non numeric)
> >
> > I'm using Oracle / PLSQL, note the above example is a small set of
> > data
> >
> > Any help appreciated
> > Thanks
> >
> > Dave
>
>
> select your_col
> from your_table
> where length(your_col) = 3
> and translate(your_col, '123456789', '000000000') != '000';
>
> Regards
> //Rauf Sarwar

Thankyou everyone for you examples, whilst using the examples I also noticed that my 2 digits values contain white space so I'm also having to include the rtrim statement to ignore these.

Thanks

Dave Received on Fri Aug 02 2002 - 12:03:43 CEST