如何在SQL Server存储过程中从列表中删除电子邮件地址?

在一个存储过程中，有一个报告每天发送给大约25个收件人。我想从列表中删除一个电子邮件id。

假设我想删除abc@test.com，我该怎么做?

3个邮箱地址:abc@test.com; 123@test.com; 456@test.com

这是我正在尝试的代码:

BEGIN TRANSACTION
UPDATE [BCCDW].[Config].[StoredProcExecution] 
SET Recipients = '123@test.com;456@test.com'
WHERE SPName = 'p_rpt_daily_alarm'
COMMIT;

请尝试以下解决方案。它将从SQL Server 2017开始工作。

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Recipients VARCHAR(MAX));
INSERT INTO @tbl (Recipients) VALUES
('abc@test.com; 123@test.com; 456@test.com');
-- DDL and sample data population, end
-- before
SELECT * FROM @tbl;
DECLARE @emailRemove VARCHAR(100) = 'abc@test.com';
UPDATE @tbl
SET Recipients = TRIM(' ;' FROM REPLACE(Recipients, @emailRemove, ''));
-- after
SELECT * FROM @tbl;

前

+----+------------------------------------------+
| ID |                Recipients                |
+----+------------------------------------------+
|  1 | abc@test.com; 123@test.com; 456@test.com |
+----+------------------------------------------+

+----+----------------------------+
| ID |         Recipients         |
+----+----------------------------+
|  1 | 123@test.com; 456@test.com |
+----+----------------------------+

相关内容

最新更新

热门标签：