sql - 使用 STRING_SPLIT() 评估列

Question

我有一个表格，其中一列在一个字符串中包含多个电子邮件地址或名称:

+----+-------------------------------------------------------+
| id ¦ emails                                                ¦
+----+-------------------------------------------------------+
| 1  ¦ John Doe; jdoe@gmail.com; Company IT; jd@company.com; ¦
+----+-------------------------------------------------------+
| 2  ¦ jane@company.com; Jane Doe; jd@company.com;           ¦
+----+-------------------------------------------------------+

我需要评估电子邮件列以确定是否有外部电子邮件地址(例如 jdoe@gmail.com)。因此，我需要将 emails 列中的值拆分为单独的字符串并评估每个字符串。最后，我想用一个额外的列来更新表，以指示电子邮件是内部的还是外部的。

+----+-------------------------------------------------------+-----------+
| id ¦ emails                                                ¦ flag      ¦
+----+-------------------------------------------------------+-----------+
| 1  ¦ John Doe; jdoe@gmail.com; Company IT; jd@company.com; ¦ external  ¦
+----+-------------------------------------------------------+-----------+
| 2  ¦ jane@company.com; Jane Doe; jd@company.com;           ¦ internal  ¦
+----+-------------------------------------------------------+-----------+

我设法使用 STRING_SPLIT() 拆分了一个字符串，并执行了我想要的(某种)求值。

DECLARE @flag int

SELECT @flag=COUNT(*) FROM STRING_SPLIT('John Doe; jdoe@gmail.com; Company IT; jd@company.com;', ';')
WHERE value LIKE '%@%'AND value NOT LIKE '%Company%'

IF (@flag > 0)
BEGIN
Print 'extern'
END
ELSE 
BEGIN
Print 'intern'
END

但是，现在我将一个字符串传递给 STRING_SPLIT() 函数。我想改为传递列 emails 并根据评估结果更新表格。有什么想法可以实现吗？

Answer 1

您可以在 select 中执行此操作:

select t.*,
       (case when e.cnt > 0 then 'external' else 'internal' end) as flag
from t cross apply
     (select count(*) as cnt
      from string_split(t.emails, ';') s
      where value LIKE '%@%' AND value NOT LIKE '%Company%'
     ) e;

注意:这保留了 OP 识别“内部”与“外部”的原始逻辑。问题似乎与此无关。显然，此版本混淆了用户名和域组件，因此用户名实际上可能包含公司名称，即使 OP 可能希望它是“外部”。

通常，我会警告在单个列中存储多个值，推荐更规范化的结构——每行一封电子邮件的表格。我确实了解有时电子邮件列表仅用于传递给电子邮件程序并且需要采用特定格式。在这种情况下，它们通常被视为黑盒，通常不会在 SQL 中进行解析，因此它们是该规则的合理异常(exception)。