Re: translate function

Hello!

Can you use the REPLACE function in lieu of the TRANSLATE? Perhaps something like:

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

(This is effectively REPLACing the comma with an empty string.) Maybe you could do the same thing with TRANSLATE? (Or does TRANSLATE not affect the actual *size* of the target string?)

I'm new to Oracle, so I dunno if this'll work, but I thought I'd throw my $0.02 in. ;-)

Hope this helps!

John Peterson

"mlm" <not_at_listed.com> wrote in message news:3AC253F0.EBC37046_at_listed.com...
> 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 Wed Mar 28 2001 - 19:14:36 CST