SQL>
SQL>
SQL> CREATE TABLE old_item (
2 item_id CHAR(20),
3 item_desc CHAR(25)
4 );
Table created.
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>
SQL> SELECT DECODE(SUBSTR(item_id, 1, 2),
2 'LA', 'Western',
3 'NY', 'Eastern',
4 '* Unknown *'
5 ) "Region",
6 SUBSTR(item_id, 4,3) "Item ID",
7 item_desc
8 FROM old_item;
Region Ite ITEM_DESC
----------- --- -------------------------
Western 101 Can, Small
Western 102 Can, Large
Western 103 Bottle, Small
Western 104 Bottle, Large
Eastern 101 Box, Small
Eastern 102 Box, Large
Eastern 103 Shipping Carton, Small
Eastern 104 Shipping Carton, Large
8 rows selected.
SQL>
SQL> drop table OLD_ITEM;
Table dropped.
SQL>
SQL>
SQL>