Thanks for the question, Dante.
Asked: December 01, 2016 - 12:19 am UTC
Last updated: December 01, 2016 - 1:05 am UTC
Version: 11 g
Viewed 1000+ times
You Asked
Hi Team,
I need to update values for a column in my table to null, which are not numeric & greater than 999.
Here is sample table -
create table checknum
(
col1 varchar2(10),
col2 varchar2(10)
);
Insert into CHECKSUM (COL1,COL2) values ('1','100');
Insert into CHECKSUM (COL1,COL2) values ('2','999');
Insert into CHECKSUM (COL1,COL2) values ('3','10001');
Insert into CHECKSUM (COL1,COL2) values ('4','abcd');
Insert into CHECKSUM (COL1,COL2) values ('5','9999a');
Insert into CHECKSUM (COL1,COL2) values ('6','12343a@#');
Output -
col1 col2
1 100
2 999
3 null
4 null
5 null
6 null
I have done it in 2 steps
a) Update all non numeric to null
b) Update all values greater than 999 to null.
Can we do this in single step ?? Please assist.
and Connor said...
You can use a REGEXP for this
SQL> create table checknum
2 (
3 col1 varchar2(10),
4 col2 varchar2(10)
5 );
Table created.
SQL>
SQL> Insert into checknum (COL1,COL2) values ('1','100');
1 row created.
SQL> Insert into checknum (COL1,COL2) values ('2','999');
1 row created.
SQL> Insert into checknum (COL1,COL2) values ('3','10001');
1 row created.
SQL> Insert into checknum (COL1,COL2) values ('4','abcd');
1 row created.
SQL> Insert into checknum (COL1,COL2) values ('5','9999a');
1 row created.
SQL> Insert into checknum (COL1,COL2) values ('6','12343a@#');
1 row created.
SQL>
SQL> update checknum
2 set col2 =
3 case when regexp_like(col2,'^[+-]?([0-9]*\.?[0-9]+|[0-9]+\.?[0-9]*)([eE][+-]?[0-9]+)?$') and to_number(col2) <= 999
4 then to_number(col2)
5 end;
6 rows updated.
SQL>
SQL> select *
2 from checknum ;
COL1 COL2
---------- ----------
1 100
2 999
3
4
5
6
6 rows selected.
SQL>