Find and replace functionality an in update?

[Unbridled]

I am looking for the syntax to add information to information that is already in a field. I am trying to add or replace a value to a field (org_perm_id) for those that have not posted in 30 days on a message forum.

UPDATE ibf_members;
SET org_perm_id = ',8,12,'
WHERE last_post < timestampadd(SQL_TSI_DAY, -30,
current_date()) 

Is there anyway to say: set org_perm_id to just ADD 12 (I haven't posted in 30 days mask) to the existing values?

So if our members and their respective masks (masks used to visit private forums) are: (with 6 being a member in good standing):

Tara 6, 9, 10
Sue 6, 8, 9, 10
Gina 6, 10

I want to change their masks to be:
Tara 9, 10, 12
Sue 8, 9, 10, 12
Gina 10, 12

with a find "6" and replace with "12"??? Is there find and replace functionality in SQL in a field or just the ability to add one value to a field without changing the existing data?

[Unbridled]

Any hope with something like this? I've been googling my little brains out.

UPDATE emp set designation=replace(designation, 'Sales', 'Marketing') where department='Marketing'

[ronverdonk]

Yes, you could, but what if you don't want to replace only, but also concatenate? Maybe then you could use a combination of MySQL CONCAT() and REPLACE() functions (I am not that familiar with the latter one).

I think it may be easier to use a programming language (such as PHP) to SELECT the row, 'explode' the values, replace them individually, 'implode' them and UPDATE them back into the row again.

Ronald :cool:

[miller]

Agreed.

This is something that you could use a REGEXP to translate, but this is not the best way of handling your sql table. Instead following ronverdonk's advice and just programmatically solve this: splitting the field into it's parts, maninpulating the data to add your "12" mask, and then updating the database record.