Re: select only data with numbers from alphanumeric field

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 Wed Oct 16 2002 - 11:05:45 CDT