Mega Code Archive

 

Categories / Oracle PLSQL / Char Functions

 

Ise INSTR to format a column

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 item_desc,   2         INSTR(item_desc,   3               ',',   4               1   5               )   6  FROM   old_item; ITEM_DESC                 INSTR(ITEM_DESC,',',1) ------------------------- ---------------------- Can, Small                                     4 Can, Large                                     4 Bottle, Small                                  7 Bottle, Large                                  7 Box, Small                                     4 Box, Large                                     4 Shipping Carton, Small                        16 Shipping Carton, Large                        16 8 rows selected. SQL> SQL> SQL> drop table OLD_ITEM; Table dropped.