pseudocode pseudocode - 5 months ago 10
SQL Question

How To Specific Replace in MsSQL?

I have a problem.

I want to replace all columns which start with 2,finish with 2 and contain 2 inside.
For example for my column;


[Numbers]

1, 2, 22, 33, 4, 5

2, 3, 42, 25, 6

12, 28, 62, 2


I want to replace "only 2" (not 22 or 25 etc.) with X. Here is my query:

UPDATE mytable
set Numbers = replace(Numbers, ',2', ',X') WHERE Numbers like '%,2'

UPDATE mytable
set Numbers = replace(Numbers, ',2,', ',X,') WHERE Numbers like '%,2,%'

UPDATE mytable
set Numbers = replace(Numbers, '2,', 'X,') WHERE Numbers like '2,%'


But output is not true. This code replace all 2 in the column with X.

Can anyone help me? How can I write true query?

Answer

Try this then remove ',' (first and end)

SELECT REPLACE(',' + REPLACE('1, 2, 22, 33, 4, 5', ' ', '') + ',', ',2,', ',X,') -- ,1,X,22,33,4,5,
SELECT REPLACE(',' + REPLACE('2, 3, 42, 25, 6', ' ', '') + ',', ',2,', ',X,') -- ,X,3,42,25,6,
SELECT REPLACE(',' + REPLACE('12, 28, 62, 2', ' ', '') + ',', ',2,', ',X,') -- ,12,28,62,X,

Query looks like:

UPDATE mytable
SET Numbers = SUBSTRING(NewNumbers, 2, LEN(NewNumbers) - 2)
FROM
(
    SELECT 
       Id, 
       REPLACE(',' + REPLACE(Numbers, ' ', '') + ',', ',2,', ',X,')  AS NewNumbers
    FROM
        mytable
) A
WHERE
    mytable.Id = A.Id
    -- Your where clause