Extract domain name without domain extension using function SQL

Question

I have to extract just domain name in email without domain extension. Example I have email address katad@hotmail.com and I need just hotmail as output so I can compare with known and accepted domain list.

I tried to use this but it's not output I was hoping for:

SUBSTRING(subscribers_email,(CHARINDEX('@',subscribers_email)+1),(CHARINDEX('.',subscribers_email)))

2nd example: I have email as

d.katana@us.army.mil

, I will need

us

as domain and when comparing to domain list will result as valid. 3rd example: I have email as Dra.Katana@us.army.mil, I will need

us and when compared it should be valid, currently it would be null as domain for some reason.

That is certainly great question, I have not come across any emails with sub-domains as of yet, not sure exactly what I should do in that case. :) — DKCroat, Apr 21 '21 at 19:57

Yitzhak Khabinsky · Accepted Answer · 2021-04-21T20:37:10.613

1

One more solution.

SQL

DECLARE @email VARCHAR(100)='katad@hotmail.com';

SELECT @email AS email
    ,  PARSENAME(REPLACE(@email, '@', '.'), 2) AS domain;

Output

+-------------------+---------+
|       email       | domain  |
+-------------------+---------+
| katad@hotmail.com | hotmail |
+-------------------+---------+

SQL #2

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, email VARCHAR(100));
INSERT INTO @tbl (email) VALUES 
('katad@hotmail.com'),
('john.m.cappelletti@sub.domain.com'),
('d.katana@us.army.mil'),
('dra.m.katanad@gmail.com'),
('drazen.i.katanic@abc.com');
-- DDL and sample data population, end

SELECT *
    , PARSENAME(SUBSTRING(email, CHARINDEX('@', email) + 1, 100), 2) AS domain
FROM @tbl;

Output

+----+-----------------------------------+---------+
| ID |               email               | domain  |
+----+-----------------------------------+---------+
|  1 | katad@hotmail.com                 | hotmail |
|  2 | john.m.cappelletti@sub.domain.com | domain  |
|  3 | d.katana@us.army.mil              | army    |
|  4 | dra.m.katanad@gmail.com           | gmail   |
|  5 | drazen.i.katanic@abc.com          | abc     |
+----+-----------------------------------+---------+

edited Apr 21 '21 at 20:37

answered Apr 21 '21 at 19:53

Yitzhak Khabinsky

18,471
2
15
21

Awesome @Yitzhak Khabinsky, this works, thank you much! – DKCroat Apr 21 '21 at 19:55
@DKCroat Careful... edge events like john.m.cappelletti@sub.domain.com would fail – John Cappelletti Apr 21 '21 at 19:58
what would you do in that case @Yitzhak Khabinsky? – DKCroat Apr 21 '21 at 19:59
@DKCroat, please edit your original question, and add sample data, and expected output. – Yitzhak Khabinsky Apr 21 '21 at 20:00
@DKCroat, please try SQL #2. – Yitzhak Khabinsky Apr 21 '21 at 20:08
@Yitzhak Khabinsky I tried sql # 2, I have email like dra.m.katanad@gmail.com and after using sql #2 domain would be null – DKCroat Apr 21 '21 at 20:21
@DKCroat, I extended data sample without touching the `SELECT` statement. Check it out again. – Yitzhak Khabinsky Apr 21 '21 at 20:25
@YitzhakKhabinsky I have email like draz.m.katani@gmail.com domain is null, or email draze.katanics@us.army.mil domain is null or drazen.i.katanic@abc.com domain is null etc. – DKCroat Apr 21 '21 at 20:31
@DKCroat, I am providing you a minimal reproducible example. You copy it as-is to SSMS, and run it. So far, everything is working as the doctor ordered. – Yitzhak Khabinsky Apr 21 '21 at 20:38
@YitzhakKhabinsky, you have right, I certainly apologize, I was totally looking in some other field, my had and what's in there is not functioning as should :), thank you so much for your help – DKCroat Apr 21 '21 at 20:47
@DKCroat, glad to hear that the proposed solution is working for you. It is based on **tokenization**. No need to count for any position of dots, hold intermediate results, `CROSS APPLY`, etc. – Yitzhak Khabinsky Apr 21 '21 at 20:49
@YitzhakKhabinsky you helped me a lot, thank you so much! – DKCroat Apr 21 '21 at 20:50
@DKCroat, Please mark it as answered. You simply need to mark an answer as correct (the green check image). Click the green outlined checkmark to the left of the answer that solved your problem. This marks the answer as "accepted" – Yitzhak Khabinsky Apr 21 '21 at 21:01

score 0 · Answer 2 · answered Apr 21 '21 at 19:34

0

This works for your example, you can add additional replace clauses if needed for other top-level names

declare @email varchar(100)='katad@hotmail.com'
select Replace(Right(@Email, Len(@Email) - CharIndex('@', @email)),'.com','')

answered Apr 21 '21 at 19:34

Stu

30,392
6
14
33

so with this I would get hotmailcom, so I need just hotmail, .com or .net or anything else needs to be removed – DKCroat Apr 21 '21 at 19:41

score 0 · Answer 3 · answered Apr 21 '21 at 20:35

Here is a solution that returns the first level domain regardless of any sub-domains:

Declare @testData Table (email varchar(100));
 Insert Into @testData (email)
 Values ('katad@hotmail.com'), ('john.m.cappelleti@sub.domain.com'), ('Dra.Katana@us.army.mil'), ('d.katana@us.army.mil');

 Select *
      , domain = substring(td.email, p1.pos, p2.pos - p1.pos)
   From @testData                                               As td
  Cross Apply (Values (charindex('@', td.email, 1) + 1))        As p1(pos)
  Cross Apply (Values (charindex('.', td.email, p1.pos)))       As p2(pos);

Results from sample table:

score 0 · Answer 4 · answered Apr 21 '21 at 20:40

You need to start the search from after the @

CROSS APPLY (VALUES works wonders for holding intermediate values

SELECT
    SUBSTRING(subscribers_email, v1.atsign, v2.dot - v1.atsign) 
FROM table
CROSS APPLY (VALUES (NULLIF(CHARINDEX('@', subscribers_email), 0) ) ) v1(atsign)
CROSS APPLY (VALUES (NULLIF(CHARINDEX('.', subscribers_email, v1.atsign), 0) + 1 ) ) v2(dot)

Extract domain name without domain extension using function SQL

4 Answers4