Use substr and instr to extract column value : INSTR « Char Functions « Oracle PL / SQL






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

Use substr and instr to extract column value

  
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                1,
  4                INSTR(item_desc,
  5                      ',',
  6                      1
  7                      ) -1
  8                ) CATEGORY,
  9         SUBSTR(item_desc,
 10                INSTR(item_desc,
 11                      ',',
 12                      1
 13                      ) +2,
 14                99
 15                ) ITEM_SIZE
 16  FROM   old_item;

ITEM_DESC                 CATEGORY                  ITEM_SIZE
------------------------- ------------------------- -------------------------
Can, Small                Can                       Small
Can, Large                Can                       Large
Bottle, Small             Bottle                    Small
Bottle, Large             Bottle                    Large
Box, Small                Box                       Small
Box, Large                Box                       Large
Shipping Carton, Small    Shipping Carton           Small
Shipping Carton, Large    Shipping Carton           Large

8 rows selected.

SQL>
SQL> drop table OLD_ITEM;

Table dropped.

SQL>
SQL>
SQL>








Related examples in the same category

1.INSTR: returns the first-occurrence position of a character within a string
2.Simple demo for INSTR function: returns a location within the string where search pattern begins
3.INSTR: Look for the second occurrence of 'is'
4.If search pattern is not in the string, the INSTR function returns 0
5.Combine INSTR and SUBSTR together
6.If the INSTR pattern is not found, then the entire string would be returned
7.Get the sub string position by using instr
8.use instr in an if statement in PL SQL
9.Ise INSTR to format a column
10.split column value with ','
11.instr and substr