我有一张表,其中一列包含一个字符串中的多个电子邮件地址或名称:
+----+-------------------------------------------------------+
| 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
列,并根据评估结果更新表。任何想法如何实现这一目标?