Query to identify columns/fields having special characters in Oracle

To identify the special characters in one column / field in  oracle you can use like operator or REGEXP.

It would be better to use REGEXP instead of like. Here is the sample query to find special characters.

select * from table where regexp_like(, '[0-9\-\@\<\>\_\!\@\#\$\%\^\&\*\(\)\;\"\?\=]');

In the above query you have to specify the special character's which you want to identify.

or you can use


select * from table  where not regexp_like(nama_lgkp, '.*[^a-zA-Z0-9]')

In the above query no need to specify the special characters it will give you all the special characters.