SELECT *
FROM <table>
WHERE REGEXP_LIKE(<column>, '[^[:digit:]]');
SELECT *
FROM table
WHERE regexp_like(column_name,'[^0-9]+');
Symbol:-
=========
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[#]');
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[,]');
Character:-
=========
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[\z]');
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[\t]');
Number:-
=========
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[1]');
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[1-9]');
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[1-2]');
Table Process:-
=============
select * from special_char;
SCHAR
-------
abc$
abc
13489
kos*
289#
select * from special_char
where regexp_like(schar,'[^[:alnum:]]');
SCHAR
---------
abc$
kos*
289#
FROM <table>
WHERE REGEXP_LIKE(<column>, '[^[:digit:]]');
SELECT *
FROM table
WHERE regexp_like(column_name,'[^0-9]+');
Symbol:-
=========
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[#]');
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[,]');
Character:-
=========
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[\z]');
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[\t]');
Number:-
=========
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[1]');
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[1-9]');
select * from ap_suppliers where regexp_like (VENDOR_NAME,'[1-2]');
Table Process:-
=============
select * from special_char;
SCHAR
-------
abc$
abc
13489
kos*
289#
select * from special_char
where regexp_like(schar,'[^[:alnum:]]');
SCHAR
---------
abc$
kos*
289#
I am Sridevi Koduru, Senior Oracle Apps Trainer With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.
ReplyDeleteContact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Financial for Indian Localization, Oracle Apps SCM, Oracle Apps HRMS, SQL, PL/SQL and D2K at training@oracleappstechnical.com or sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com
Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/
Could you please help me with my below question.
DeleteThanks
Hello,
ReplyDeleteI am trying to identify fields with special characters and I am using this function below.
select ADDRESS1 from ps_PERSONAL_DATA where not regexp_like(ADDRESS1,'[A-Za-z0-9()+,-. /:?]+');
This does work well but does it is also omitting some fields like #. I need to know what I am doing work. It works for some of the fields but not the entire length of the field.
Please Advise