One way to do it without multiple replace is to use openjson and string_agg in a common table expression to get the values needed, and then update the table joined to the cte. Please note that string_agg is supported on 2017 version or higher.
First, create and populate sample table (Please save us this step in your future questions):
DECLARE @T AS TABLE
(
Numbers varchar(50)
)
INSERT INTO @T(Numbers) VALUES
('[1,33,5,4,5]'),
('[1,2,555,4,5]'),
('[1,5,3,4,5]'),
('[1,25,3,4,5]'),
('[1,2,5,4,5]'),
('[1,2,3,4,55]');
The cte:
WITH CTE AS
(
SELECT Numbers, '[' + string_agg([Value], ',') +']' As NewNumbers
FROM @T
CROSS APPLY
(SELECT [Value] FROM OPENJSON(Numbers)) As x
WHERE [Value] != 5
GROUP BY Numbers
)
The update:
UPDATE T
SET Numbers = NewNumbers
FROM @T As T
JOIN CTE ON T.Numbers = CTE.Numbers
Validate:
SELECT *
FROM @T
Result:
Numbers
[1,33,4]
[1,2,555,4]
[1,3,4]
[1,25,3,4]
[1,2,4]
[1,2,3,4,55]
You can see a demo on DB<>Fiddle.
However, the replace option is much shorter and will work with any version of SQL Server - even as old as 2000 (I think):
UPDATE @T
SET Numbers =
REPLACE(
REPLACE(
REPLACE(Numbers, '[5,', '[')
, ',5]', ']')
, ',5,', ',');
In conclusion, if you are working on 2017 or higher, and need to remove multiple values from an array, the cte + string_agg apporach will probably be easier (since all you have to do is change the where clause in the cte).
For older versions, or for a single value removal, The replace approach might be a better choice.
