SQL>
SQL> CREATE TABLE old_item (
2 item_id CHAR(20),
3 item_desc CHAR(25)
4 );
SQL>
SQL> INSERT INTO old_item VALUES('LA-101', 'Can, Small');
1 row created.
SQL> INSERT INTO old_item VALUES('LA-102', 'Can, Large');
1 row created.
SQL> INSERT INTO old_item VALUES('LA-103', 'Bottle, Small');
1 row created.
SQL> INSERT INTO old_item VALUES('LA-104', 'Bottle, Large');
1 row created.
SQL> INSERT INTO old_item VALUES('NY-101', 'Box, Small');
1 row created.
SQL> INSERT INTO old_item VALUES('NY-102', 'Box, Large');
1 row created.
SQL> INSERT INTO old_item VALUES('NY-103', 'Shipping Carton, Small');
1 row created.
SQL> INSERT INTO old_item VALUES('NY-104', 'Shipping Carton, Large');
1 row created.
SQL>
SQL> SELECT SUBSTR(item_id, 1, 2) MFGR_LOCATION,
2 SUBSTR(item_id, 4, 3) ITEM_NUMBER,
3 item_desc
4 FROM old_item;
MF ITE ITEM_DESC
-- --- -------------------------
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
MF ITE ITEM_DESC
-- --- -------------------------
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
MF ITE ITEM_DESC
-- --- -------------------------
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
32 rows selected.
SQL>
SQL> drop table OLD_ITEM;
Table dropped.
SQL>
SQL>