无效电子邮件丢失.com末尾，如何添加

Question

我有大约 1,600 封电子邮件缺少.com在电子邮件末尾。如何将缺失的.com添加到所有 1,600 封电子邮件中？

例如，我们数据库中的电子邮件地址就像colondoris5@gmail，我需要在所有gmail，yahoo，hotmail和ymail帐户的末尾添加.com。

这是我的选择语句，它生成显示所有错误电子邮件的结果：

SELECT DISTINCT
C.CustomerID,
C.BAN_ADSL_TN,
C.LandlinePhoneNumber,
C.MobilePhoneNumber,
C.EmailAddress,
C.CreatedBy,
C.ModifiedBy,
C.CreateDate
FROM dbo.Customers AS C
WHERE NOT C.EmailAddress LIKE '%_@__%.__%'
AND C.EmailAddress != ''
AND PATINDEX('%[^a-z,0-9,@,.,_,-]%', C.EmailAddress) = 0
ORDER BY C.CreateDate DESC

Answer 1

只需将.com附加到"电子邮件地址"列：

SELECT DISTINCT
C.CustomerID,
C.BAN_ADSL_TN,
C.LandlinePhoneNumber,
C.MobilePhoneNumber,
(case when c.EmailAddress like '%hotmail%' OR
c.EmailAddress like '%gmail%' OR
c.EmailAddress like '%yahoo%' OR
c.EmailAddress like '%ymail%'
then c.EmailAddress + '.com'
else c.EmailAddress
end)
C.CreatedBy,
C.ModifiedBy,
C.CreateDate
FROM dbo.Customers AS C
WHERE NOT C.EmailAddress LIKE '%_@__%.__%'
AND C.EmailAddress != ''
AND PATINDEX('%[^a-z,0-9,@,.,_,-]%', C.EmailAddress) = 0
ORDER BY C.CreateDate DESC

Answer 2

我会说：

(case when c.EmailAddress not like '%.com' and
c.EmailAddress not like '%.edu' and
c.EmailAddress not like '%.org' and
c.EmailAddress not like '%.gov'  -- and maybe more!
then c.EmailAddress + '.com'
else c.EmailAddress
end)

您可能会做出一个很大的假设，即顶级域是.com。 许多其他是常用的。

请注意，您的查询发现除了缺少顶级域之外，电子邮件地址还有其他问题。 而且它将错过域名由两部分以上但缺少顶级域名的电子邮件。

Answer 3

如果您对select语句返回的记录表示无效记录感到满意，则可以使用相同的where子句创建update。

UPDATE Customers
SET EmailAdress = EmailAddress + '.com'
WHERE NOT EmailAddress LIKE '%_@__%.__%' AND EmailAddress != '' AND PATINDEX('%[^a-z,0-9,@,.,_,-]%', C.EmailAddress) = 0

Answer 4

最简单的方法是

update e
set emailaddress = emailaddress + '.com'   -- or concat(emailaddress,'.com')
from emaildb e
where charindex('.com',emailaddress) = 0   -- only looking for no .com suffix