To All,
Below is code using Oracle 10G regexp_like to list only rows that contains only alphabets or only numbers in it.

create table test2 (text1 varchar2(30));

insert into test2 values(,);
commit;

insert into test2 values(,,);
commit;

insert into test2 values(,,,,);
commit;

insert into test2 values(,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,);
commit;

insert into test2 values(,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,);
commit;

insert into test2 values(1,2,3,4,5,6);
commit;

insert into test2 values(12);
commit;

insert into test2 values(1aaaa);
commit;

insert into test2 values(AAAA,BBBB,CCCC);
commit;

insert into test2 values(AAAAAAAAAA,BBBBBBBBBB,CC);
commit;

insert into test2 values(AAAAAAAAAA,BBBBBBBBBB,CC);
commit;

insert into test2 values(E);
commit;

insert into test2 values(XA);
commit;

insert into test2 values(a, b, c, d, e);
commit;

insert into test2 values(abcdefg);
commit;

--Select all rows that have only alphabets in the text1 column
--It does not select rows that has a comma or a digit in it
select text1
from test2
where regexp_like(text1, '^[^\,^\0-9]{1,30}$')
/

--Select all rows that have only numbers in the text1 column
--It does not select rows that has a comma or an alphabet in it
select text1
from test2
where regexp_like(text1, '^[^\,^\a-z A-Z]{1,30}$')
/

Oracle LazyDBA home page