Re: SQL: Translate

From: Phil Nguyen <philn_at_optigfx.optigfx.com>
Date: 15 Apr 92 23:35:41 GMT
Message-ID: <1355_at_optigfx.optigfx.com>


In article <1992Apr15.165953.1542_at_news2.cis.umn.edu> ayers_at_shark.micro.umn.edu (Timothy R. Ayers) writes:
>
>I am trying to update an address line in the database where someone
>entered an "*" or "**" at the end of each mailbox number. So the records
>look like this: 2076**
> 1367*
>
>I would like to remove the "*" and replace it with spaces or null.
>Someone suggested using the TRANSLATE function, but I can't find
>much documentation on it. Does anyone have any suggestions how to make
>this function work.
>
>Thanx in advance for your help.
>
>Judi

TRANSLATE and REPLACE functions are described in the Oracle's SQL Language Reference Manual Version 6.0. REPLACE does not exist in Oracle V.5.

To fix your problem try:

update table_name set field_name = translate(field_name,'*',' ') where field_name like '%*%';

or

update table_name set field_name = replace(field_name,'*','') where field_name like '%*%';

Hope this helps.

Phil Received on Thu Apr 16 1992 - 01:35:41 CEST

Original text of this message