Remove Apostrophe and Space from all the rows of a colum

Hi Vitaliy,

I have a huge table in Oracle containing 149,000 rows. I see that there are 
many fields under the first_name and last_name columns having Apostrophe (') 
symbol in them, Eg: O'Connor.

I need to remove these apostrophe from all the names for running some scripts 
for testing which does not entertain symbols in the fields. I dont want to 
revert the apostrophes too and keep the changes forever.

I also have the table data exported to an Excel sheet.

Thanks!

-- first take a quick backup of the data
--
create table_name_bak as select * from table_name;


-- this update statement will replace all 
-- single quotes [chr(39)] with a space:
--
update table_name
set first_name = replace(first_name,chr(39),' ')
,   last_name =  replace(last_name,chr(39),' ');



##
### EXAMPLE
##

SQL> create table table_name(
  2  first_name varchar2(10),
  3  last_name varchar2(10));

Table created.

SQL> insert into table_name
  2  values('FName'||chr(39)||'s','O'||chr(39)||'LastName');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_name;

FIRST_NAME LAST_NAME
---------- ----------
FName's    O'LastName

SQL> update table_name
2    set first_name = replace(first_name,chr(39),' ')
3    ,   last_name =  replace(last_name,chr(39),' ');

1 row updated.

SQL> select * from table_name;

FIRST_NAME LAST_NAME
---------- ----------
FName s    O LastName

SQL>

If you don't want to replace with space just replace with null:

update table_name
set first_name = replace(first_name,chr(39),null)
,   last_name =  replace(last_name,chr(39),null);


And to replace spaces you can do this:

update table_name
set first_name = replace(first_name,' ',null)
,   last_name =  replace(last_name,' ',null);