How To Specific Replace in MsSQL?

Question

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?

Do yourself a favor and normalize your database. Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) where the accepted answer explains in details why it's that bad. — Zohar Peled, Jun 29 '16 at 07:58
I can't. I didn't create this database and I need just modify like I said for now. — pseudocode, Jun 29 '16 at 08:00
For example at second row; I need get this [X, 3, 42, 25, 6] instead of [2, 3, 42, 25, 6] but the output is [X, 3, 4X, 25, 6] — pseudocode, Jun 29 '16 at 08:03
For the input [1, 2, 22, 33, 4, 5] the output should be [1, X, 22, 33, 4, 5]? — Babbillumpa, Jun 29 '16 at 08:07
Yes, There is a problem in collumns which start with 2 and finish with 2. I edited my comment. — pseudocode, Jun 29 '16 at 08:10

neer · Accepted Answer · 2016-06-29T08:26:15.463

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

score 0 · Answer 2 · answered Jun 29 '16 at 08:15

It is just an idea...

UPDATE mytable
set Numbers = 'X,'+substring(Numbers,3) WHERE Numbers like '2,%'

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

UPDATE mytable
    set Numbers = substring(Numbers,1,LENGTH(Numbers)-2)+',X' WHERE Numbers like '%,2'

How To Specific Replace in MsSQL?

2 Answers2