Re: translate function

I would use the replace function in conjunction with the instr function. If you need an example let me know.

mlm wrote:

> 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.
Received on Thu Mar 29 2001 - 06:57:30 CST