add a value to a field if it doesn't exist

Question

How would I 'add' data to a field if it doesn't exist?

Let's say I have 4 rows. And a column, say "datafield" looks like this:

        datafield
row001: |a|
row002: |b||c|
row003: |b||d|
row004: 

How would I create a mySQL query, such that it "adds" the value "|b|" if it doesn't exist in the row? (Not 'replacing' the data, but rather 'adding' to it).

So both row001 & row004 would get "|b|" added to them, but row001 would now contain both "|a||b|" and row004 would now be "|b|"?

So it would look like this?

        datafield
row001: |a||b|
row002: |b||c|
row003: |b||d|
row004: |b|

Answer 1

Is this what you want?

update mytable
set datafield = concat(coalsece(datafield, ''), '|b|')
where datafield is null or datafield not like '%|b|%'

This phrases as: if datafield is null or doesn't contain '|b|', append '|b|' to datafield.

Answer 2

you can use locate() function to check if |b| existed

update tableA
set datafield = concat(coalesce(datafield, ''), '|b|')
where locate('|b|', coalesce(datafield, '')) = 0