Re: select only data with numbers from alphanumeric field

Marc,
Your wish to reduce your typing effort may have backfired, as my calculations below illustrate.

Characters avoided: 29

Characters typed to explain omission of characters avoided: 66

(Don't you just hate a smart-arse?)

marc parkinson wrote:

>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 Fri Oct 18 2002 - 15:22:59 CDT