translate function

We have a table that has columns with values like:

Mclean, VA
Philadelphia, PA

We want to remove the commas in the column values. I chose to use the TRANSLATE function.

select translate(city_state, ',' , ' ') from table1;

returns:
Mclean VA
Philadelphia PA

Notice that there are now 2 spaces in between the city and the state. Me, being anal retentive, decided that I am going to try to remove the extra space between the two words (the space that used to be inhabited by the comma).

I then run:
select TRANSLATE(city_state, ' ', ' ') from table1;

Notice that I am trying to replace 2 spaces with 1 space. This does not work. I get the same string back as exists in the database. Any ideas about how to get rid of the extra space that is the remnant of the first TRANSLATE function? Or is there are cleaner way to strip out characters all together?

Oh and by the way, you cannot do:

TRANSLATE(column1, ',', '')

(replace commas with a NULL string). Oracle simply returns NULL for the entire function. This is documented behavior and is b/c of the way that Oracle deals with NULLs.

--
The views expressed here are  mine and do not represent my company in
any way.