Hi All,
Could you please help me in writing a query to filter out the numbers and special characters if exists.
DB Version : 12C
-->I need to include a condition as to check the below criteria as well:-
1. Condition to check whether the data exist only with numbers in the first_name coulmn. If exists then it should be filtered. (E.g) 9, 99, 1000,0
2. Condition to check whether the data exist only with special characters in the first_name coulmn. If exists then it should be filtered. (E.g) -,.,..,@,$,etc..,
CREATE TABLE TEST_NAME(FIRST_NAME VARCHAR2(100))
/
insert into TEST_NAME VALUES('E*******************C');
insert into TEST_NAME VALUES('宏*******司');
insert into TEST_NAME VALUES('N/A');
insert into TEST_NAME VALUES('99');
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');
insert into TEST_NAME VALUES('CLINTON4');
insert into TEST_NAME VALUES('BILL.');
insert into TEST_NAME VALUES('M*****B');
insert into TEST_NAME VALUES('-');
COMMIT;
/
SELECT * FROM TEST_NAME
WHERE FIRST_NAME NOT IN ('99','.','..','4','-','4')
/