Write a query to display Extract the code from these address

Functions used:

• INSTR() – Finds the position of the comma
• SUBSTR() – Extracts the required part of the string
• TRIM() – Removes unwanted spaces

Source Data

ADDR
Hyderabad, HYD
Banglore, BANG
Mumbai, MUM
Delhi, DEL

Expected Target

ADDR	CODE
Hyderabad, HYD	HYD
Banglore, BANG	BANG
Mumbai, MUM	MUM
Delhi, DEL	DEL

1. Create the Sample Table

Drop the table if it already exists:

DROP TABLE t_addr;

Create the table:

CREATE TABLE t_addr
(
    addr VARCHAR2(20)
);

2. Insert Sample Data

INSERT INTO t_addr VALUES ('Hyderabad, HYD');
INSERT INTO t_addr VALUES ('Banglore, BANG');
INSERT INTO t_addr VALUES ('Mumbai, MUM');
INSERT INTO t_addr VALUES ('Delhi, DEL');

COMMIT;

View the data:

SELECT *
FROM t_addr;

3. Step 1: Find the Position of the Comma Using INSTR()

The INSTR() function returns the position of the comma in the address.

SELECT addr,
       INSTR(addr, ',') AS col
FROM t_addr;

Output:

ADDR	COL
Hyderabad, HYD	10
Banglore, BANG	9
Mumbai, MUM	7
Delhi, DEL	6

How INSTR works:
INSTR(addr, ',') searches the ADDR column and returns the position where the comma is found.

4. Step 2: Extract the String Starting from the Comma

Now use SUBSTR() with the position returned by INSTR().

SELECT addr,
       INSTR(addr, ',') AS col,
       SUBSTR(addr, INSTR(addr, ',')) AS code
FROM t_addr;

Output:

ADDR	COL	CODE
Hyderabad, HYD	10	, HYD
Banglore, BANG	9	, BANG
Mumbai, MUM	7	, MUM
Delhi, DEL	6	, DEL

5. Step 3: Remove the Comma and Space Using +2

The comma occupies one character and the space after the comma occupies another character. Therefore, we add 2 to the comma position.

SELECT addr,
       SUBSTR(addr, INSTR(addr, ',') + 2) AS code
FROM t_addr;

Output:

ADDR	CODE
Hyderabad, HYD	HYD
Banglore, BANG	BANG
Mumbai, MUM	MUM
Delhi, DEL	DEL

6. Alternative Method: Using TRIM()

Instead of assuming that exactly one space exists after the comma, we can start immediately after the comma and use TRIM() to remove leading and trailing spaces.

SELECT addr,
       TRIM(SUBSTR(addr, INSTR(addr, ',') + 1)) AS code
FROM t_addr;

Output:

ADDR	CODE
Hyderabad, HYD	HYD
Banglore, BANG	BANG
Mumbai, MUM	MUM
Delhi, DEL	DEL

INSTR, SUBSTR, and TRIM Explained

Function	Purpose
INSTR()	Finds the position of a character or substring
SUBSTR()	Extracts part of a string
TRIM()	Removes leading and trailing spaces

Recommended Query

The TRIM version is generally more flexible because it does not depend on exactly one space appearing after the comma.

SELECT addr,
       TRIM(SUBSTR(addr, INSTR(addr, ',') + 1)) AS code
FROM t_addr;

Key Point:
Use INSTR() to locate the delimiter, SUBSTR() to extract the required part of the string, and TRIM() to remove unwanted spaces.