Use SUBSTR to decode a column : SUBSTR « Char Functions « Oracle PL / SQL

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

Use SUBSTR to decode a column

  
SQL>
SQL> CREATE TABLE old_item (
  2       item_id   CHAR(20),
  3       item_desc CHAR(25)
  4       );


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 SUBSTR(item_id, 1, 2) MFGR_LOCATION,
  2         SUBSTR(item_id, 4, 3) ITEM_NUMBER,
  3         item_desc
  4  FROM   old_item;

MF ITE ITEM_DESC
-- --- -------------------------
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small

MF ITE ITEM_DESC
-- --- -------------------------
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large

MF ITE ITEM_DESC
-- --- -------------------------
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large
LA 101 Can, Small
LA 102 Can, Large
LA 103 Bottle, Small
LA 104 Bottle, Large
NY 101 Box, Small
NY 102 Box, Large
NY 103 Shipping Carton, Small
NY 104 Shipping Carton, Large

32 rows selected.

SQL>
SQL> drop table OLD_ITEM;

Table dropped.

SQL>
SQL>

   
  

Related examples in the same category