Extract Email Domains Using INSTR() and SUBSTR() in Oracle SQL

SQL Challenge #98: How to Extract an Email Domain Using INSTR() and SUBSTR() in Oracle SQL

Sometimes, while working with employee or customer records, you may need to get the domain name separately from an email address.

For example:

• [e-mail] → gmail.com
• [e-mail] → yahoo.com

Let's see how to do this using two simple Oracle SQL functions.

The Problem

You have an "employees" table containing email addresses.

You need to write a query that extracts everything after the "@" symbol and displays it in a separate column.

Input: employees

emp_id	email
101	[e-mail]
102	[e-mail]
103	[e-mail]
104	[e-mail]
105	[e-mail]

Your Challenge

Write a query to:

1. Find the position of "@" in each email.
2. Extract everything after "@".
3. Display the email and its domain.
4. Keep the original table data unchanged.

Think about the query before looking at the solution!

Oracle SQL Solution

SELECT emp_id,
email,
SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM employees;

Expected Output

emp_id	email	domain
101	[e-mail]	gmail.com
102	[e-mail]	yahoo.com
103	[e-mail]	outlook.com
104	[e-mail]	company.in
105	[e-mail]	gmail.com

How Does It Work?

Let's understand the two functions used in this query.

1. INSTR()

"INSTR()" finds the position of a character in a string.

SELECT INSTR('[e-mail]', '@')
FROM dual;

Output:

7

The "@" symbol is at position 7.

1. SUBSTR()

"SUBSTR()" extracts characters from a given position.

SELECT SUBSTR('[e-mail]', 8)
FROM dual;

Output:

gmail.com

1. Using Both Functions Together

SUBSTR(email, INSTR(email, '@') + 1)

First, "INSTR()" finds the position of "@".

Then, we add 1 to start from the next character.

Finally, "SUBSTR()" extracts the remaining characters.

That's how we get the email domain in a single query.

Note: This query assumes that the email address contains an "@" symbol. Invalid email addresses should be checked separately.

Your Turn

Now try the opposite.

Suppose you have:

[e-mail]

You want to extract only:

kailas

Which SQL function or combination of functions would you use?

Write your query in the comments. Let's see how you solve it!

Follow for more practical SQL questions and solutions.

SQL #OracleSQL #SQLChallenge #DataAnalytics #LearnSQL #SQLFunctions #Database