Find and remove special character in Oracle database table.

gchen91789
gchen91789 used Ask the Experts™
on
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?

Thanks,
Comment
Watch Question

Do more with

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

Commented:
- 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:
http://www.roubaixinteractive.com/PlayGround/Binary_Conversion/The_Characters.asp
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 ;

Thanks,

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