jquery - delete a value in comma delimited string in PHP

Solution:

Please try the following solution.

It will work starting from SQL Server 2012 onwards.

It is using XML and XQuery to tokenize email list, and remove not needed email. You can package it as a stored procedure with two parameters, @reportName and @emailToRemove.

The CTE does all the heavy lifting:

1. Converts email list into XML data type.
2. Using XQuery to eliminate not needed email.
3. Converts back to comma separated email list.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, reportName VARCHAR(30), ShareWith VARCHAR(1024));
INSERT INTO @tbl (reportName, ShareWith) VALUES
('IBM SH  data', '[email protected],[email protected],[email protected]'), 
('Samsung Sr', '[email protected],[email protected]'),
('Xiaomi MFG', ''),
('Apple US st', '[email protected],[email protected]'),
('LG EU', '[email protected]');
-- DDL and sample data population, end

DECLARE @reportName VARCHAR(30) = 'IBM SH  data'
    , @emailToRemove varchar(30) = '[email protected]'
    , @separator CHAR(1) = ',';

;WITH rs AS
(
    SELECT *
        , REPLACE(TRY_CAST('<root><r><![CDATA[' + 
                 REPLACE(ShareWith, @separator, ']]></r><r><![CDATA[') + ']]></r></root>' AS XML)
                 .query('
                 for $x in /root/r[lower-case((./text())[1]) ne lower-case(sql:variable("@emailToRemove"))]
                 return data($x)
                 ').value('.', 'VARCHAR(MAX)'), SPACE(1), @separator) AS modifiedShareWith
    FROM @tbl
    WHERE reportName = @reportName
)
UPDATE t
SET ShareWith = rs.modifiedShareWith
FROM @tbl AS t
    INNER JOIN rs ON t.id = rs.id;

 -- test
 SELECT * FROM @tbl;
 
Copy code

Output

+----+
Copy code