2

I have a table tbl_data. There is an attribute with name price where comma separated values i need to divide these comma separated values by a particular number.

price=2,4,6

I need something

update tbl_data set price=price/2

result required = 1,2,3

Alex M
  • 2,756
  • 7
  • 29
  • 35
Prashant Pandey
  • 343
  • 3
  • 4
  • Explode the array, do your calculations, implode the array = result – Andy Holmes Jul 19 '16 at 09:54
  • I do not have flexibility to use php . – Prashant Pandey Jul 19 '16 at 09:57
  • 1
    Wow, this is ugly. I'd actually suggest that you rethink your schema since it looks as though you really have a relationship there that should be in another table. However... http://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql – David Hoelzer Jul 19 '16 at 10:00
  • You could use a [stored procedure](http://dev.mysql.com/doc/refman/5.7/en/stored-routines.html). But personally I would export the data to my localhost (or another server running PHP) and make the changes there and then copy/import the data back to the original DB. – Matt Browne Jul 19 '16 at 11:11

1 Answers1

0
update tbl_data set price = replace( replace(price, ',', ''), '"', '' )/2;

by using this query 2,4,6 converted to 123 then try to insert comma by character wise with group_concat() function

Matt Browne
  • 12,169
  • 4
  • 59
  • 75
Keyur Buch
  • 41
  • 4