EasyCFM.COM ColdFusion Forums / Oracle Related Issues / migrating data from columnA to columnB in same tbl

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: migrating data from columnA to columnB in same tbl

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

dlackey
09-07-2010 @ 1:17 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

I have a table in oracle that contains two data columns.  ColA is blank but ColB contains 10 sentences.  I need to take the first sentence of each record in ColB and copy it into ColA but I dont know the syntax.  Can anyone here help me with this?

dlackey
09-07-2010 @ 2:05 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

Found my answer...  substr as in the following example:

CREATE TABLE old_item (
       item_id   CHAR(20),
       item_desc CHAR(25)
       );



INSERT INTO old_item VALUES('LA-101', 'Can, Small');
INSERT INTO old_item VALUES('LA-102', 'Can, Large');
INSERT INTO old_item VALUES('LA-103', 'Bottle, Small');
INSERT INTO old_item VALUES('LA-104', 'Bottle, Large');
INSERT INTO old_item VALUES('NY-101', 'Box, Small');
INSERT INTO old_item VALUES('NY-102', 'Box, Large');


SELECT item_desc,
         SUBSTR(item_desc,
                1,
                INSTR(item_desc,
                      ',',
                      1
                      ) -1
                )
  FROM   old_item;


Cheers,
Chip


Website Designed and Developed by Pablo Varando.