Re: finding rows matching pattern

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