jquery - delete a value in comma delimited string in PHP
Solution:
You desperately need to fix the schema. Storing multiple values in a string is just wrong. But sometimes we are stuck with other people's really, really, really bad decisions.
I would suggest this structure:
UPDATE table
SET shareWith = TRIM(',' FROM REPLACE(CONCAT(',', ShareWIth, ','), CONCAT(',', ?, ',')) )
WHERE reportName = ? AND
CONCAT(',', shareWith, ',') LIKE CONCAT('%,', ?, ',%');
That is, simplify the logic by putting delimiters around the searched string and the pattern.
However, I will repeat: you should have a table with one value per report and share.
Instead of the more flexible TRIM(), you can use spaces and TRIM() and REPLACE() instead:
SET shareWith = REPLACE(TRIM(REPLACE(REPLACE(CONCAT(',', ShareWIth, ','), CONCAT(',', ?, ','))), ',', ' '), ' ', ',')
This assumes that the string doesn't have spaces, which makes sense for emails in a string.
Answer
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:
- Converts email list into XML data type.
- Using XQuery to eliminate not needed email.
- 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;
Output
+----+