[Resolved] Need to do UPDATE - REPLACE - REGEXP transaction

[ppowell]

Here is my original query:

UPDATE contacts SET joined_contact_codes = REPLACE(joined_contact_codes, 'blah', 'foo') WHERE joined_contact_codes REGEXP ',?blah,?'

Old output:

blah,bar.baz

New output:

foo,bar,baz

This should replace every instance of 'blah' with 'foo' in every entry in the contacts table. And it works, of course, however, there is this scenario:

UPDATE contacts SET joined_contact_codes = REPLACE(joined_contact_codes, 'blah', '') WHERE joined_contact_codes REGEXP ',?blah,?'

Old output

blah,bar.baz

New INTENDED output

bar,baz

BUT this is what you wind up with

,bar,baz

The commas are not taken out along with "blah", which is a problem when dealing with the row output in future queries.

How would you best deal with this other than having to do another UPDATE - REPLACE - REGEXP after the first one? This will be dealing with several thousand, perhaps up to several dozen thousand records.

Thanx
Phil

[ppowell]

GOT IT! Three different REPLACE functions on the same field:

UPDATE contacts SET
joined_contacts_list = REPLACE(joined_contacts_list, ',blah,', ','),
joined_contacts_list = REPLACE(joined_contacts_list, ',blah', ''),
joined_contacts_list = REPLACE(joined_contacts_list, 'blah,', '')
WHERE joined_contacts_list REGEXP ',?blah,?'

It would also work for editing too:

UPDATE contacts SET
joined_contacts_list = REPLACE(joined_contacts_list, ',blah,', ',foo,'),
joined_contacts_list = REPLACE(joined_contacts_list, ',blah', ',foo'),
joined_contacts_list = REPLACE(joined_contacts_list, 'blah,', 'foo,')
WHERE joined_contacts_list REGEXP ',?blah,?'

Phil