How to replace specific value of column in Sql Server

I have a table and in this one column which have comma separated value like '1,3,5,44'. Table structure is like below

Id	CityIds	IsActive
1	2,44,32,86,33,3	1
2	3,43,19,16	1

I want to replace some specific value like 3 to 5 in CityIds column.Please note i want to replace only 3 to 5 not 33 to 5 .

Dear Topic , Thanks for your question & i explain you below please have a look .

Solution is

CREATE TABLE CodeTable
(
ID int ,
CityIds varchar(100),
IsActive bit
)

Insert some data in above table

INSERT INTO CodeTable
SELECT 1,'2,44,32,86,33,3',1
UNION
SELECT 2,'3,43,19,16',1

After insert data then data look like above . Now Query for above condition is given below.

DECLARE @ValueToBeReplace varchar(10)
DECLARE @ValueReplaceBy varchar(10)

SET @ValueToBeReplace='3'
SET @ValueReplaceBy='5'

UPDATE CodeTable SET CityIds= REPLACE(','+REPLACE(','+CityIds+',',','+@ValueToBeReplace+',',','+@ValueReplaceBy+',')+',',',,','')

SELECT * FROM CodeTable

Output Is:

ID	CityIds	IsActive
1	2,44,32,86,33,5	1
2	5,43,19,16	1

Explanation:

Here i declare two variable 1st is @ValueToBeReplace and 2nd is @ValueReplaceBy . 1st value is user want to replace & 2nd is value which take place of 1st value . IN Query you see that i just use REPLACE Function.I hope you aware about this if not then click on replace function . First we add , to both side of column value like this  ','+CityIds+','  .Since we know that REPLACE function takes 3 parameter In first parameter we set ','+CityIds+','   in second we set ','+@ValueToBeReplace+','
 and in third we set ','+@ValueReplaceBy+',' . Another REPLACE function is used for remove the first and last comma which we add in columns.

Note: Code Tested in SQL SERVER 2008.

read more...