Mega Code Archive
Use substr and instr to extract column value
<![CDATA[
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> SELECT item_desc,
2 SUBSTR(item_desc,
3 1,
4 INSTR(item_desc,
5 ',',
6 1
7 ) -1
8 ) CATEGORY,
9 SUBSTR(item_desc,
10 INSTR(item_desc,
11 ',',
12 1
13 ) +2,
14 99
15 ) ITEM_SIZE
16 FROM old_item;
ITEM_DESC CATEGORY ITEM_SIZE
------------------------- ------------------------- -------------------------
Can, Small Can Small
Can, Large Can Large
Bottle, Small Bottle Small
Bottle, Large Bottle Large
Box, Small Box Small
Box, Large Box Large
Shipping Carton, Small Shipping Carton Small
Shipping Carton, Large Shipping Carton Large
8 rows selected.
SQL>
SQL> drop table OLD_ITEM;
Table dropped.
SQL>
SQL>
SQL>
]]>