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