Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: finding rows matching pattern

Re: finding rows matching pattern

From: Harald Maier <maierh_at_myself.com>
Date: Tue, 26 Aug 2003 12:16:40 +0200
Message-ID: <m38ypgoh9z.fsf@ate.maierh>


ronaldf_at_eml.cc (Ronald Fischer) writes:

> Say, I have a column C of type varchar. Is it possible using SQL to
> find all rows, where C contains only characters from a specific set
> - for example, only digits? That is, a row where C contains '52063'
> should be selected, but a row where C contains '81BF42' should not
> be selected.
>
> Ronald

You may try the translate function.

,----
| drop table notonlynumbers;
| create table notonlynumbers (
| val varchar2(10) constraint notonlynumbers_pk primary key
| );
|
| insert into notonlynumbers values ( '12' );
| insert into notonlynumbers values ( '1A' );
| insert into notonlynumbers values ( '13' );
|
| select val
| from notonlynumbers
| where translate(val, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'X') not like '%X%';
`----

Harald Received on Tue Aug 26 2003 - 05:16:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US