Re: select only data with numbers from alphanumeric field

I tried [substr(col,11,1) between 0 and 9], but received and error. I will try yours, but by the looks of it, it seems much better.

Thanks much for the help!

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3DAD8E50.1E72DB67_at_exesolutions.com>...
> Richard Brust wrote:
>
> > 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...
>
> SELECT ....
> FROM ...
> WHERE SUBSTR(col, 1,4) = 'ABCD'
> AND SUBSTR(col,11,1) IN ('0', '1', '2', '3', .... , '9')
>
> Is one way.
>
> Daniel Morgan
Received on Thu Oct 17 2002 - 18:04:01 CDT