使用函数SQL提取没有域扩展的域名

Question

我必须提取仅在电子邮件中的域名没有域名扩展。例如，我有电子邮件地址katad@hotmail.com，我只需要hotmail作为输出，以便我可以比较已知和接受的域名列表。

我试着使用它，但它不是我所希望的输出：

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

第二个例子:我有电子邮件

d.katana@us.army.mil

，我需要

我们

作为域，当与域进行比较时，列表将作为有效结果。第三个例子:我有电子邮件作为Dra.Katana@us.army.mil，我需要

us，如果比较，它应该是有效的，目前由于某种原因，它将为空域。

Answer 1

还有一个解决办法。

SQL

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

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

输出

+-------------------+---------+
|       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;

输出

+----+-----------------------------------+---------+
| 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     |
+----+-----------------------------------+---------+

Answer 2

对于您的示例来说，如果需要，可以为其他顶级名称添加额外的replace子句。

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

Answer 3

下面是一个返回第一级域的解决方案，而不考虑任何子域：

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);

抽样表的结果：

​

​