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.
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.