Replace a value of comma separated string

[watsmyname]

Hey there

I m sorry if its a repost, i tried to search with no results.

 

I have mysql table and i have a field  which holds value something like sometimes 65, sometimes 65,34, sometimes 65,34,76 and so on. All i need is that i have to match certain value in these comma separated fields [finding i know, we use FIND_IN_SETS], but my problem is i have to find particular value and replace with another

 

For instance, i have to search 76 and its found in 65,34,76. Now i have to replace this 76 with 67 to make our set look like 65,34,67.

 

I hope my query is clear, can any body show me the way to achieve this with mysql?

 

Thanks

[watsmyname]

Well i came up with this code and seems to work, but not sure if it work always can anybody tell me

 

update tablename SET fieldname=(select replace(fieldname,'76','67')) WHERE FIND_IN_SET('76',fieldname)>0

[watsmyname]

well it wont work always, say i have  set 176,65,34,76 above sql code replaces first string too along with matching last one. That is it will change set to 167,65,34,67

 

can anybody help??

[fenway]

You have to search for a complete value -- wrapped in commas -- and wrap your field value in commas as well.  That's why storing delimited values is a very bad idea.

 

update tablename SET fieldname=(select replace( CONCAT(',',fieldname,','), ',76,', ',67,')) WHERE FIND_IN_SET('76',fieldname)>0

[watsmyname]

Thanks mate

 

My problem is that i have to work with database made by someone else long ago. The reason a field have comma separated value is because a song might have two artists, so in song table they've put the value in comma separated form.

 

Next your mysql code works, but it adds extra comma like ,176,65,34,67. And next time when i search for 176, its position will be 2 not 1, isn't there another work around??

[watsmyname]

I got it working here is my code

 

update tablename SET fieldname=TRIM(BOTH ',' from (select replace(CONCAT(',',fieldname,','), ',76,', ',67,'))) WHERE FIND_IN_SET('76',fieldname)>0