Home » SQL & PL/SQL » SQL & PL/SQL » Need a simple(?) logic (Oracle 10g)
Need a simple(?) logic [message #385266] Sun, 08 February 2009 16:09 Go to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi Friends,

I need a simple(?) logic.

CREATE TABLE HEADER
  (HEADER_DESC VARCHAR2(200)
  );
  
 CREATE TABLE LINES
  (
    COL1 VARCHAR2(50) ,
    COL2 VARCHAR2(20) ,
    COL3 VARCHAR2(100)
  );
  
 INSERT INTO HEADER VALUES
  ('xyzinformation - 123456 - Documentinformation'
  );

My requirement is to insert the header information into Lines table as 
COL1 = xyzinformation
COL2= 123456
COL3= Documentinformation


The values from the header table are separated by '-'(hyphen).

Thanks in advance.


Re: Need a simple(?) logic [message #385268 is a reply to message #385266] Sun, 08 February 2009 17:48 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
INSERT INTO LINES VALUES ('xyzinformation','123456','Documentinformation');
Re: Need a simple(?) logic [message #385269 is a reply to message #385266] Sun, 08 February 2009 18:37 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hope this may help you.

insert into lines 
select substr(header_desc,1,instr(header_desc, '-',1)-1), 
trim (both '-' from regexp_substr(header_desc, '- [^,]*-') ), 
substr(header_desc, instr(header_desc,'-',1,2)+1) 
from test;

[Updated on: Mon, 09 February 2009 00:29] by Moderator

Report message to a moderator

Re: Need a simple(?) logic [message #385296 is a reply to message #385269] Mon, 09 February 2009 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lakshmis,

Indent the code (See SQL Formatter) and Make sure that lines of code do not exceed 80 characters.

Regards
Michel
Re: Need a simple(?) logic [message #385307 is a reply to message #385266] Mon, 09 February 2009 00:56 Go to previous messageGo to next message
st33chen
Messages: 11
Registered: January 2009
Junior Member
hi,

maybe this is more clear (ignore performance issue) :

INSERT INTO LINES
SELECT SUBSTR(HEADER_DESC,1,POS1-1), 
       SUBSTR(HEADER_DESC,POS1+3,POS2-POS1-3), 
       SUBSTR(HEADER_DESC, POS2+3)
  FROM (
       SELECT HEADER_DESC, 
              INSTR(HEADER_DESC,' - ',1,1) POS1, 
              INSTR(HEADER_DESC,' - ',1,2) POS2
         FROM HEADER
       )  
Re: Need a simple(?) logic [message #385309 is a reply to message #385269] Mon, 09 February 2009 01:09 Go to previous message
gynanda
Messages: 33
Registered: November 2007
Member
Thanks. It helps me
Previous Topic: Issueing COMMIT in Exception (merged 3)
Next Topic: Avoid Duplicate
Goto Forum:
  


Current Time: Wed Dec 07 05:11:23 CST 2016

Total time taken to generate the page: 0.09347 seconds