Re: Replace unwanted chars in data with a space?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 24 Jul 2001 13:25:14 -0700
Message-ID: <9jklja0to4@drn.newsguy.com>

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 Tue Jul 24 2001 - 15:25:14 CDT