Find and remove special character in Oracle database table.

gchen91789 used Ask the Experts™
In oracle 11g database, there is a special character 'Â' in the database table.
How can I find out how many records have special character in it? and how can I remove it?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

- kindly refer to the ascii table (as below) and find the ascii number for that character (i think its 194) in the ascii table then query as follows:

select * from tablename where columnname like '%' ||chr(194) || '%'

- to remove use replace function:

update  tablename set columnname = replace(columnname,chr(194),'')

ascii table:
I try the SQL statement you provide in SQLPlus, it return entire table.
I try change the chr from 192-197, same result.

I search the net and find the SQL statement below:

select workstation_name from workstation where regexp_like(workstation_name, '[^[:alnum:]/''%()> -_.:=;[]');

It return 4 records from the table.

Can you help me to convert the regexp_like to regexp_replace to remove the special character? does the follow SQL will work?

select regexp_replace(workstation_name, '[^[:alnum:]/''%()> -_.:=;[]', '') from workstation ;


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial