Mega Code Archive

 

Categories / Oracle PLSQL / Conversion Functions

 

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>