RE: Modify data from select before insert.

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Thu, 19 Aug 2004 15:15:31 -0400
Message-ID: <543DF856D23431489D4B8028C300FBAB08AC93BE@exchsen0a1mb>

Lyndon,

Look up the substr command, as well as translate and replace. In your examples, your query would look like:

insert into table1
(col1)
select replace(substr(col1,1,3) || '-' || substr(col1,4,100),'A','B') from tabl2;

Would add the dash and replace all 'A' with 'B'.

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

From: Lyndon Tiu [mailto:ltiu_at_alumni.sfu.ca] Sent: Thursday, August 19, 2004 3:04 PM
To: oracle-l_at_freelists.org
Subject: Modify data from select before insert.

Hello guys,

I could use a few tips here.

I have a sql statement:

insert into table1
(
col1
)
select col1 from tabl2;

Problem is, I need to modify the data in the column from table2 first before inserting to table1. Like add a dash, after the third character ... chnage all A's to B's sort of thing.

Can you guys give me a few hints and perhaps a few keywords I can use to search google for?

Thank you very much.

--

Lyndon Tiu

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu Aug 19 2004 - 14:11:11 CDT