Hi,
DB version : 12C
I have a scenario to exclude and to pull out invalid values present in the database. please Assist.
Valid Data samples;-
| Slno | Valid Data |
| 1 | b***********3 |
| 2 | **************** |
| 3 | Michael |
| 4 | 5*********b |
| 5 | (NULL) |
CREATE TABLE TEST_NAME(FIRST_NAME VARCHAR2(100),MIDDLE_NAME VARCHAR2(100),LAST_NAME VARCHAR2(100))
/
insert into TEST_NAME VALUES('E*******************C','','B*********M');
insert into TEST_NAME VALUES('宏*******司','','');
insert into TEST_NAME VALUES('N/A','','');
insert into TEST_NAME VALUES('99','','DAVID');
insert into TEST_NAME VALUES('LANGLEVELDT','','');
insert into TEST_NAME VALUES('宏司','宏司','宏司');
insert into TEST_NAME VALUES('.','..','');
insert into TEST_NAME VALUES('NA','','');
insert into TEST_NAME VALUES('..','','');
insert into TEST_NAME VALUES('4','','Z****N');
insert into TEST_NAME VALUES('CLINTON4','','');
insert into TEST_NAME VALUES('BILL.','','');
insert into TEST_NAME VALUES('M*****B','','3');
insert into TEST_NAME VALUES('-','','');
insert into TEST_NAME VALUES('45787CLINTON','','');
COMMIT;
/
--SQL QUERY:-
select first_name,middle_name,last_name
from test_name
where (regexp_like(first_name, '^([[:digit:]]+$)|([^[:alnum:]]+)$')
or regexp_like(middle_name, '^([[:digit:]]+$)|([^[:alnum:]]+)$')
or regexp_like(last_name, '^([[:digit:]]+$)|([^[:alnum:]]+)$'))
/
Desired Output:-
| First_name | Middle_name | Last_name |
| N/A | | |
| 99 | | DAVID |
| . | .. | |
| NA | | |
| .. | | |
| 4 | | Z****N |
| M*****B | | 3 |
| - | | |