我有一列存储多个电子邮件，分号分隔如下：
| Email |
| -----------------------------|
| abc@y.aa; abc@z.bb |
| pqr@y.bb; pqr@x.cc; pqr@z.dd |
| xyx@y.dd; xyx@z.pp |
我希望使用分隔符分号拆分电子邮件，并将它们存储在不同的列中。
我尝试了一些字符串命令，但它没有为我提供合适的结果
REVERSE(PARSENAME(REPLACE(REVERSE(Email), ';', '.'), 1)) AS [Email1],
REVERSE(PARSENAME(REPLACE(REVERSE(Email), ';', '.'), 2)) AS [Email2],
REVERSE(PARSENAME(REPLACE(REVERSE(Email), ';', '.'), 3)) AS [Email3]
如有任何线索，我们将不胜感激。
如果有更好的解决方案，请随时在这里分享。
提前感谢！
这里有两种方法：
DECLARE @email TABLE (Email VARCHAR(1000));
INSERT @email VALUES('abc@y.aa; abc@z.bb'),('pqr@y.bb; pqr@x.cc'),('xyx@y.dd; xyx@z.pp');
--==== 1. Using APPLY
SELECT
EmailGroup = e.Email,
Email = email1.E1
FROM @email AS e
CROSS APPLY (VALUES
( SUBSTRING(e.Email, 0, CHARINDEX(';', e.email) ) ),
( SUBSTRING(e.Email, CHARINDEX(';', e.email) + 1, 8000) )
) AS email1(E1);
--==== 2. Using STRING_SPLIT
SELECT e.Email, split.[value]
FROM @email AS e
CROSS APPLY STRING_SPLIT(e.Email,';') AS split;
两者返回：
EmailGroup Email
----------------------- -------------
abc@y.aa; abc@z.bb abc@y.aa
abc@y.aa; abc@z.bb abc@z.bb
pqr@y.bb; pqr@x.cc pqr@x.cc
pqr@y.bb; pqr@x.cc pqr@y.bb
xyx@y.dd; xyx@z.pp xyx@y.dd
xyx@y.dd; xyx@z.pp xyx@z.pp
对于同一行的两封电子邮件：
SELECT
EmailGroup = e.Email,
Email1 = SUBSTRING(e.Email,0,CHARINDEX(';',e.email)),
Email2 = SUBSTRING(e.Email,CHARINDEX(';',e.email)+1,8000)
FROM @email AS e;
EmailGroup Email1 Email2
---------------------- ---------- ---------------
abc@y.aa; abc@z.bb abc@y.aa abc@z.bb
pqr@y.bb; pqr@x.cc pqr@y.bb pqr@x.cc
xyx@y.dd; xyx@z.pp xyx@y.dd xyx@z.pp
如果您有已知或最大数量的电子邮件地址，您可以使用一点JSON
示例
Select A.[email]
,Email1 = JSON_VALUE(S,'$[0]')
,Email2 = JSON_VALUE(S,'$[1]')
,Email3 = JSON_VALUE(S,'$[2]')
From YourTable A
Cross Apply ( values ( '["'+replace(replace(email,' ',''),';','","')+'"]' ) ) B(S)
结果
email Email1 Email2 Email3
abc@y.aa; abc@z.bb abc@y.aa abc@z.bb NULL
pqr@y.bb; pqr@x.cc pqr@y.bb pqr@x.cc NULL
xyx@y.dd; xyx@z.pp xyx@y.dd xyx@z.pp NULL
试试这个：
Declare @testTable Table (Email varchar(100));
Insert Into @testTable (Email)
Values ('abc@a.aa')
, ('abc@y.aa; abc@z.bb')
, ('pqr@y.bb; pqr@x.cc; pqr@z.dd')
, ('xyx@y.dd; xyx@z.pp');
Select tt.Email
, email1 = ltrim(substring(v.email, 1, p01.pos - 2))
, email2 = ltrim(substring(v.email, p01.pos, p02.pos - p01.pos - 1))
, email3 = ltrim(substring(v.email, p02.pos, p03.pos - p02.pos - 1))
From @testTable As tt
Cross Apply (Values (concat(tt.Email, replicate(';', 3)))) As v(email)
Cross Apply (Values (charindex(';', v.email, 1) + 1)) As p01(pos)
Cross Apply (Values (charindex(';', v.email, p01.pos) + 1)) As p02(pos)
Cross Apply (Values (charindex(';', v.email, p02.pos) + 1)) As p03(pos)
您已经接近PARSENAME了，但该函数解析句点，而电子邮件中有句点，这会使它失效。
您可以用另一个字符替换这些字符，然后在末尾将它们翻转回句点。
例如
SELECT
REPLACE(REVERSE(PARSENAME(REVERSE(REPLACE(REPLACE(Email, '.', '`'), ';', '.')),1)), '`','.') AS Email1,
REPLACE(REVERSE(PARSENAME(REVERSE(REPLACE(REPLACE(Email, '.', '`'), ';', '.')),2)), '`','.') AS Email2,
REPLACE(REVERSE(PARSENAME(REVERSE(REPLACE(REPLACE(Email, '.', '`'), ';', '.')),3)), '`','.') AS Email3,
REPLACE(REVERSE(PARSENAME(REVERSE(REPLACE(REPLACE(Email, '.', '`'), ';', '.')),4)), '`','.') AS Email4