split column value with ',' : INSTR « Char Functions « Oracle PL / SQL

1. Oracle PL / SQL
2. Char Functions
3. INSTR

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>

   
  

Related examples in the same category