If you want a more robust solution (for not only 1,2 and 7 but any wished sequences) use this solution:
First, Create a split function. Just copy/paste this (credits to this answer) code:
CREATE FUNCTION Split (@InputString varchar(8000),
@Delimiter varchar(50))
RETURNS @Items TABLE (
Item varchar(8000)
)
AS
BEGIN
IF @Delimiter = ' '
BEGIN
SET @Delimiter = ','
SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
END
IF (@Delimiter IS NULL
OR @Delimiter = '')
SET @Delimiter = ','
DECLARE @Item varchar(8000)
DECLARE @ItemList varchar(8000)
DECLARE @DelimIndex int
SET @ItemList = @InputString
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
WHILE (@DelimIndex != 0)
BEGIN
SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
INSERT INTO @Items
VALUES (@Item)
-- Set @ItemList = @ItemList minus one less item
SET @ItemList = SUBSTRING(@ItemList, @DelimIndex + 1, LEN(@ItemList) - @DelimIndex)
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
END -- End WHILE
IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
BEGIN
SET @Item = @ItemList
INSERT INTO @Items
VALUES (@Item)
END
-- No delimiters were encountered in @InputString, so just return @InputString
ELSE
INSERT INTO @Items
VALUES (@InputString)
RETURN
END -- End Function
GO
Edit: As per @Sean Lange comment, please consider a better performance split function as described here.
Second, here is the code:
DECLARE @table TABLE (
ID int,
UserID int,
Items varchar(50)
);
INSERT INTO @table (ID, UserID, Items)
VALUES (1, 54, '1,2,3,4,5,6'), (2, 55, '1,2,5,6,7,8'), (3, 56, '1,2,3,4,5,6'),
(4, 57, '1,2,3,4,5,6'), (5, 58, '1,2,3,4,5,6'), (6, 59, '1,2,3,4,5,6'), (7, 54, '1,2,3,4,5,6');
DECLARE @toBeRemoved TABLE (
num varchar(16)
);
INSERT INTO @toBeRemoved (num)
VALUES ('1'), ('2'), ('7');
DECLARE @forUserIds TABLE (
userId int
);
INSERT INTO @forUserIds (userId)
VALUES (54), (55), (56), (57), (58);
SELECT
ID,
UserID,
Items,
LEFT([Sub].[NewItems], LEN([Sub].[NewItems]) - 1) AS 'NewItems'
FROM (SELECT DISTINCT
ST2.*,
(SELECT
ST1.Item + ',' AS [text()]
FROM (SELECT
*
FROM @table a
CROSS APPLY dbo.Split(Items, ',')
WHERE Item NOT IN (SELECT
num
FROM @toBeRemoved)) ST1
WHERE ST1.ID = ST2.ID
ORDER BY ST1.ID
FOR xml PATH (''))
[NewItems],
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY id) AS RowNo
FROM (SELECT
*
FROM @table a
CROSS APPLY dbo.Split(Items, ',')
WHERE Item NOT IN (SELECT
num
FROM @toBeRemoved)) ST2) [Sub]
WHERE RowNo = 1
AND UserID IN (SELECT
userId
FROM @forUserIds);
Notice the @toBeRemoved table and @forUserIds tables. These are exactly the tables you can change as you wish and dynamically modify. The result is dependent on these tables. The @table is just for testing purposes. Replace it by your actual table.
Edit2: Here is the sql update statement instead of select. I changed the @table to YourTable. Just use your table name instead.
UPDATE YourTable
SET Items = LEFT([Sub].[NewItems], LEN([Sub].[NewItems]) - 1)
FROM (SELECT DISTINCT
ST2.*,
(SELECT
ST1.Item + ',' AS [text()]
FROM (SELECT
*
FROM YourTable a
CROSS APPLY dbo.Split(Items, ',')
WHERE Item NOT IN (SELECT
num
FROM @toBeRemoved)) ST1
WHERE ST1.ID = ST2.ID
ORDER BY ST1.ID
FOR xml PATH (''))
[NewItems],
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY id) AS RowNo
FROM (SELECT
*
FROM YourTable a
CROSS APPLY dbo.Split(Items, ',')
WHERE Item NOT IN (SELECT
num
FROM @toBeRemoved)) ST2) [Sub]
WHERE [Sub].RowNo = 1
AND [Sub].UserID IN (SELECT
userId
FROM @forUserIds)
AND [Sub].ID = YourTable.ID;
SELECT
*
FROM YourTable
