Delete a specific value from a string (string contains comma separated values)

Question

For example, if I have a table structure like this:

Table 1
    ID Name Value

    001 Rajesh 90,100,210,400
    002 Suresh 100,400,300,66
    003 Mahesh 200,500
    004 Virat 400,578,57

How can I delete 400 from Suresh?

DELETE Value ="400" FROM table1
WHERE Name = 'Suresh'

This doesn't work.

Possible duplicate of: stackoverflow.com/questions/14642658/… Also see Docs: ftp.nchu.edu.tw/MySQL/tech-resources/articles/… To remove set elements from an existing set, we use the REPLACE function to remove the element. If using decimal values, we use a combination of the bitwise AND operator & with the bitwise NOT operator ~. UPDATE set_test SET myset = REPLACE(myset,'Dancing','') WHERE rowid = 6; UPDATE set_test SET myset = myset & ~2 WHERE rowid = 6; — xQbert
– xQbert, Commented Jul 13, 2015 at 17:03

reaanb · Accepted Answer · 2015-07-13 16:42:57Z

1

I would recommend splitting the values into a second table which is related via the person's ID. However, you can use the following query for your current situation:

UPDATE table1
SET Value = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', Value, ','), ',400,', ','))
WHERE Name = 'Suresh'

Here's a SQL Fiddle. For reference, see MySQL's string functions

answered Jul 13, 2015 at 16:42

reaanb

10.1k2 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

XXDebugger Over a year ago

is there any way we can change this in hql ?

Collectives™ on Stack Overflow

Delete a specific value from a string (string contains comma separated values)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related