Range check for numeric values in varchar2 type column
518206Jun 14 2006 — edited Jun 14 2006-------------------------------
SQL oracle 10g windows XP
--------------------------------
I have a table which has column of type varchar2 e.g
create table osa_gms_mailbox(reg_address varchar2(60),bin_address varchar2(60));
let us insert some sample rows:
insert into osa_gms_mailbox values ('8000','8000');
insert into osa_gms_mailbox values ('9000','9000');
insert into osa_gms_mailbox values ('abc1@sdd.com','abc1@sdd.com');
If i try to run the query :
select reg_address from (
select reg_address from osa_gms_mailbox
MINUS
select reg_address from osa_gms_mailbox where
regexp_like(reg_address,'^[a-z]+|[A-Z]+$')) temp
where to_number(temp.reg_address) between 1000 and 10000;
or
select reg_address from (
select reg_address from osa_gms_mailbox
MINUS
select reg_address from osa_gms_mailbox where
regexp_like(reg_address,'^[a-z]+|[A-Z]+$'))
where to_number(reg_address) between 1000 and 10000;
it gives ORA-01722: invalid number .
how can i get the numeric comparision done for varchar2 field and exclude the rows which have atleast one alphabet (a-z,A-Z)
Thanks