Ise INSTR to format a column : INSTR « Char Functions « Oracle PL / SQL

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

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.

   
  

Related examples in the same category