CODEWITHSUNDEEP
sqlsql-serverreplace
pseudocode
pseudocode

Reputation: 219

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?

Upvotes: 1

Views: 132

Answers (2)

neer
neer

Reputation: 4082

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

Upvotes: 4

Babbillumpa
Babbillumpa

Reputation: 1864

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'

Upvotes: 0

Related Questions