Remove & UPDATE all junk characters by REGEXP_REPLACE using FOR loop

Question

I have a table contains 10+ columns. Some of the data contains junks like arrows,®,¼,â... I want to remove all the junk characters from all columns value with REGEXP_REPLACE using FOR loop in UPDATE statement.

DATA Example:

I AM ‘USERâ€

?®Name

• We

I have to write a UPDATE query using FOR loop to check all columns:

update table_name
set    column_name = regexp_replace(column_name,'[^[a-z,A-Z,0-9,[:space:]]]*','')
where  regexp_like(column_name, '[^[:print:][:space:]]' );

Answer 1

You don't need a pl/sql for loop for this, just an update should be fine.

UPDATE 
  table_name 
  SET 
  column_name = REGEXP_REPLACE(str,'[^a-zA-Z0-9[:space:]]','')
 WHERE regexp_like(column_name, '[^a-zA-Z0-9[:space:]]' );

for multiple columns, just change the update statement.

create table weird_chars (
    id                             number generated by default on null as identity 
                                   constraint weird_chars_id_pk primary key,
    column1                        varchar2(100 char),
    column2                        varchar2(100 char),
    column3                        varchar2(100 char)
)
;

-- load data
 
insert into weird_chars (
    column1,
    column2,
    column3
) values (
    'I AM ‘USERâ€',
    '?®Name',
    'Just normal text'
);
-- load data
 
insert into weird_chars (
    column1,
    column2,
    column3
) values (
    'Just normal text',
    'Just normal text',
    'Just normal text'
);

UPDATE 
  weird_chars 
  SET 
  column1 = REGEXP_REPLACE(column1,'[^a-zA-Z0-9[:space:]]',''),
  column2 = REGEXP_REPLACE(column2,'[^a-zA-Z0-9[:space:]]',''),
  column3 = REGEXP_REPLACE(column3,'[^a-zA-Z0-9[:space:]]','')
  
 WHERE 
   (regexp_like(column1, '[^a-zA-Z0-9[:space:]]' ) OR 
    regexp_like(column2, '[^a-zA-Z0-9[:space:]]' ) OR 
    regexp_like(column3, '[^a-zA-Z0-9[:space:]]' )
   );

1 row updated