Re: Replace unwanted chars in data with a space?

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Wed, 25 Jul 2001 23:38:45 -0700
Message-ID: <3B5FBAF5.DAA6C189@ntsource.com>

Following the lead presented below you could also do the following:

SQL> select replace('Jordan; Rd.',';') from dual;

REPLACE('J

---

Jordan Rd.

And avoid the extra space.

Frank Hubeny

Thomas Kyte wrote:

> In article <3B5DBC05.823F455B_at_courts.state.ny.usNOSPAM>, Bob says...
> >
> >Does anyone have a way of finding an unwanted char (say, a semi colon ;
> >)in a varchar2 data field and if it is found, removing it and replacing
> >it with a space? Preferabley, I would like to include a series of chars
> >to be seached for:
> >Something to take this:
> >Jordan; Rd.
> >and leave
> >Jordan Rd.
> >These are typos in conversion data that woun't pass our edits. Thanks.
> >I've recieved much needed help from this group.
> > Bob Maggio
> >
>
> tkyte_at_TKYTE901.US.ORACLE.COM> select replace( 'Jordan; Rd.', ';', ' ' ) from
> dual;
>
> REPLACE('JO
> -----------
> Jordan Rd.
>
> works for the single character, to translate a set of characters into a set of
> other characters:
>
> tkyte_at_TKYTE901.US.ORACLE.COM> select translate( 'Jordan; Rd.', ';.', ' ' ) from
> dual;
>
> TRANSLATE('
> -----------
> Jordan Rd
>
> make the ' ' as long as the ';.'
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
  Received on Thu Jul 26 2001 - 01:38:45 CDT