Re: select only data with numbers from alphanumeric field

Do something like this

select columnname
from tablename
where columnname like 'ABCD%"
and substr (columnname , 7, 1) in ('0', '1', '2', ... '9');

note: the ... is for the other numbers I do not want to type.

Marc G. Parkinson
Senior Oracle Engineer

"Richard Brust" <brust_at_tbri.com> wrote in message news:f12180f2.0210160739.7a807e38_at_posting.google.com...
> the field is VARCHAR2(20)
>
> first four are letters
> next 7 could be a mixture of letters/numbers
>
> (most data in this column is 11 chars wide, some are more)
>
> what I want to do is create a query like this:
>
> select columnname from tablename
> where columnname LIKE 'ABCD______#';
> ---------------------------^^^^^^ (6 spaces)------
>
> so, the first four would be a specific letter sequence,
> the next 6 could be whatever,
> and the last "thing" would be a number.
>
> Thank you very much...
Received on Thu Oct 17 2002 - 21:24:11 CDT