Re: REPLACE function Question

From: Yosi Greenfield <yosi_at_comhill.com>
Date: Mon, 18 Dec 2000 13:16:02 -0500
Message-Id: <10714.124855@fatcity.com>

--------------C6921FDCB1BB12F7DC1841EF
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Try sql from sql.

set head off
SPOOL replace_all_cols.sql
SELECT 'update table1 set col1 = replace (col1, ' || string_to_replace || ', ' || new_string || ');'
FROM mask_values_tables
/
SPOOL OFF
@replace_all_cols

where mask_values_table is your table of masks, col1 is the column you want to replace, string_to_replace is the first column in your mask_values_table, and new_string is the replacing column in your mask_values_table.

hth,

Yosi

nstetson_at_csc.com wrote:

> Hi All,
>
> Has anyone ever written a script to search a text string and change all the
> occurances of a list of values to their cooresponding mask value. I know
> that the RELACE function can do this for one string value. We have a
> table of mask values. It lists what the original value is and then what
> the mask should be. We need to search a text table and convert any values
> found in this table by using our look-up mask table. Have any ideas?
>
> Thanks,
> Nancy
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: nstetson_at_csc.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
Thanks,

Yosi


---------------------------------------------------------
Yosi Greenfield
Database Architect
Comhill Systems, Inc.
yosi_at_comhill.com


--------------C6921FDCB1BB12F7DC1841EF
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
Try sql from sql.
<p><tt>set head off</tt>
<br><tt>SPOOL replace_all_cols.sql<br>
SELECT 'update table1 set col1 = replace (col1, ' || string_to_replace
|| ', ' || new_string || ');'</tt>
<br><tt>FROM&nbsp;&nbsp; mask_values_tables</tt>
<br><tt>/</tt>
<br><tt>SPOOL OFF</tt>
<br><tt>@replace_all_cols</tt>
<p>where mask_values_table is your table of masks, col1 is the column you
want to replace, string_to_replace is the first column in your mask_values_table,
and new_string is the replacing column in your mask_values_table.
<p>hth,
<p>Yosi
<br>&nbsp;
<p>nstetson_at_csc.com wrote:
<blockquote TYPE=CITE>Hi All,
<p>Has anyone ever written a script to search a text string and change
all the
<br>occurances of a list of values to their cooresponding mask value.&nbsp;
I know
<br>that the RELACE function can do this for one string value.&nbsp;&nbsp;
We have a
<br>table of mask values.&nbsp; It lists what the original value is and
then what
<br>the mask should be.&nbsp; We need to search a text table and convert
any values
<br>found in this table by using our look-up mask table.&nbsp; Have any
ideas?
<p>Thanks,
<br>&nbsp; Nancy
<p>--
<br>Please see the official ORACLE-L FAQ: <a href="http://www.orafaq.com">http://www.orafaq.com</a>
<br>--
<br>Author:
<br>&nbsp; INET: nstetson_at_csc.com
<p>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp;
FAX: (858) 538-5051
<br>San Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --
Public Internet access / Mailing Lists
<br>--------------------------------------------------------------------
<br>To REMOVE yourself from this mailing list, send an E-Mail message
<br>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
<br>the message BODY, include a line containing: UNSUB ORACLE-L
<br>(or the name of mailing list you want to be removed from).&nbsp; You
may
<br>also send the HELP command for other information (like subscribing).</blockquote>

<p>--
<br>Thanks,
<p>Yosi
<br>&nbsp;
<p>---------------------------------------------------------
<br>Yosi Greenfield
<br>Database Architect

Received on Mon Dec 18 2000 - 12:16:02 CST