String replace a string pattern?
Posted on 2007-10-11
OK I have a list of ID's stored in a field that is formatted as follows:
[id(date)],[id(date)],[id(date)], and so on...
So if data was entered into the field is would look something like:
[22(2007-09-30 00:00:00)],[500(2007-09-30 12:00:00)],[1(2007-09-30 05:00:00)], and so on...
What I want to be able to do is to remove an ID from the sequence through one query. The only data I will know for finding and removing the ID is the ID value but not the date.
I have managed to do this, but my method is not as quick as I'd like. So how do I redo my example below so that is will work with any ID from 1 to 1000000+. The only thing that changes the length of each ID in the sequence is the ID as the length of the date is constant.
Working example for ID = 1:
UPDATE table SET id_list=REPLACE(id_list, SUBSTRING(id_list, LOCATE('[1(', id_list), 25), '')
To make it work for any ID I need to replace the '25' in the example above so that it is correct for any ID.
There must be a better way to do this? Any ideas?