Tuesday 10 June 2014

how to identify the special character in oracle

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#

3 comments:

  1. 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.

    Contact 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/

    ReplyDelete
    Replies
    1. Could you please help me with my below question.

      Thanks

      Delete
  2. Hello,
    I 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

    ReplyDelete