我正在设置一个新查询,但不幸的是我卡在SQL中的某种函数中。我有一些带有特定电子邮件的记录。我想要的只是带来"@"之后的所有内容。
例如:
cesarcastillo88@hotmail.com
==> 结果我应该得到以下内容:hotmail.com
.
这并不复杂,因为记录仅显示一封电子邮件。
但。。。如果该记录包含以下电子邮件,该怎么办:
cesarcastillo88@hotmail.com ; laura23@gmail.com ; test@compliance.com
对于那些在一条记录中只有 1 封电子邮件的情况下,我做得很完美
我使用了以下公式:
substring(**columnName**, charindex('@', sfe.**columnName**), len(sfe.**columnName**))
但是,我应该如何在一条记录中使用 3 封电子邮件来做到这一点?
我期望的结果如下:
hotmail.com ; gmail.com ; compliance.com
这是一个可能的解决方案,基于假设您有某种有助于识别每个唯一行的ID
列:
;with smpl as (
select *
from (values
(1, 'cesarcastillo88@hotmail.com ; laura23@gmail.com ; test@compliance.com'),
(2, 'abc@cde.net'),
(3, 'laura23@gmail.com ; test@compliance.com')) x(id, email)
), split(id, A, B) as (
select distinct id, CAST(LEFT(email, CHARINDEX(';',email+';')-1) as varchar(100)),
CAST(STUFF(email, 1, CHARINDEX(';',email+';'), '') as varchar(100))
from smpl
union all
select id, CAST(LEFT(B, CHARINDEX(';',B+';')-1) as varchar(100)),
CAST(STUFF(B, 1, CHARINDEX(';',B+';'), '') as varchar(100))
from split
where B > ''
), clr as (
select ID, substring(LTRIM(RTRIM(A)), charindex('@', LTRIM(RTRIM(A))) + 1, len(LTRIM(RTRIM(A)))) cleanEmail
--into #tempTbl
from split
), ccat as (
SELECT DISTINCT ST2.ID,
SUBSTRING(
(
SELECT ';'+ST1.cleanEmail AS [text()]
FROM clr ST1
WHERE ST1.ID = ST2.ID
ORDER BY ST1.ID
FOR XML PATH ('')
), 2, 1000) Emails
FROM clr ST2
)
select * from ccat
以下是关于这一切如何工作的一些解释:
- 第一个 CTE 表达式使用
;
作为分隔符将电子邮件拆分为单独的行 - 第二个 CTE 基于您的功能,从电子邮件地址中删除收件人并仅离开域
- 最后一个将所有内容连接回来,并使用与分隔符相同的
;
。如果这是您喜欢的输出,请随意添加额外的空格。
你没有说是什么版本的SQL Server,但我假设是2016年或更高版本。 它们的关键是STRING_SPLIT功能。 要将其联接到您的数据,您需要使用交叉应用。
create table #a (
id int identity(1,1),
email varchar(max)
)
insert #a
values ('cesarcastillo88@hotmail.com ; laura23@gmail.com ; test@compliance.com')
, ('dannyboy@irish.com')
select id
, email
, substring(email, CHARINDEX('@', email) + 1, len(email)) as domain
from #a
select a.id
, substring(ltrim(rtrim(b.value)), CHARINDEX('@', ltrim(rtrim(b.value))) + 1, len(ltrim(rtrim(b.value)))) as domain
from #a a
cross apply string_split(email, ';') b
drop table #a