I have a table and in this one column which have comma separated value like '1,3,5,44'. Table structure is like below
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 .
(
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'
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:
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.
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...
0 comments:
Post a comment