Mega Code Archive

 

Categories / Oracle PLSQL / Char Functions

 

Split column value with

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