Decode a column with substr and instr
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 INSTR(item_desc,
4 ',',
5 1
6 ) +2,
7 99
8 )
9 FROM old_item;
ITEM_DESC SUBSTR(ITEM_DESC,INSTR(IT
------------------------- -------------------------
Can, Small Small
Can, Large Large
Bottle, Small Small
Bottle, Large Large
Box, Small Small
Box, Large Large
Shipping Carton, Small Small
Shipping Carton, Large Large
8 rows selected.
SQL>
SQL> drop table OLD_ITEM;
Table dropped.
SQL>
Related examples in the same category