SQL How to find parts of a field then put them in a new column

Question

Sorry the title is vague; I can't really find a good way to put this into a short sentence. So here is my question in the best detail I can think of:

Lets say I have the following table from a grocery store which connects several tables (by putting the Supplier_ID, Fruit_ID and Supplier contract Expiration_Date in one field which are all taken from tables I can't directly access:

FRUIT ------------ EXTERNAL_ID
Apple              12AH==F1899==2008-04-04
Banana             167E==F17621==2009-01-18
Kiwi               892==F1009==2010-01-10
Pear               110YU==F987==2009-02-22

If I need to put the first portion of the external_ID into a new column added to the end of the current table for ease of comparison. Then do the same for the middle part (which will always have 'F' at the beginning of the code) in a new column... how would I go about doing this?

Keep in mind the first part can change length, so I can't go based off length. I was thinking about possibly going until the query hits a '=' in the field but I don't know how I would do that.

EDIT: Using TOAD Oracle if it helps.

Answer 1

I did this is T-SQL before you mentioned it was for Oracle, but the principle stays the same. I will try to convert to Oracle using instr() and substr() in place of charindex(), left() and right(). The concept is to use the "=" as a delimiter and then parse the external_id into 3 new columns based on the position of that character in the string.

CREATE TABLE #myTbl
(
    fruit   CHAR(10),
    external_id CHAR(30),
    ext_num     CHAR(10),
    ext_code    CHAR(10),
    ext_date    DATETIME
)

INSERT INTO #myTbl (fruit, external_id)
VALUES('Apple','12AH==F1899==2008-04-04')

INSERT INTO #myTbl (fruit, external_id)
VALUES('Banana','167E==F17621==2009-01-18')

INSERT INTO #myTbl (fruit, external_id)
VALUES('Kiwi','892==F1009==2010-01-10')

INSERT INTO #myTbl (fruit, external_id)
VALUES('Pear','110YU==F987==2009-02-22')

SELECT * FROM #myTbl

UPDATE #myTbl
SET ext_num = LEFT(external_id,CHARINDEX('=',external_id)-1),
ext_code = LEFT(RIGHT(rtrim(external_id), len(external_id) - (CHARINDEX('=',external_id)+1)), CHARINDEX('=',RIGHT(rtrim(external_id), len(external_id) - (CHARINDEX('=',external_id)+1)-1))),
ext_date = RIGHT(rtrim(external_id),10)
FROM #myTbl

SELECT * FROM #myTbl