On 26 Sep, 15:35, mcl <mcl.off...@googlemail.com> wrote:
> On 26 Sep, 12:11, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 26 Sep, 11:47, mcl <mcl.off...@googlemail.com> wrote:
>
> > > On 26 Sep, 11:19, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > On 26 Sep, 11:08, mcl <mcl.off...@googlemail.com> wrote:
>
> > > > > I am very new to MySQL and programming and I have some errors in a
> > > > > field in my database
>
> > > > > Field = 'Brian, Bill,Joe, Peter'
>
> > > > > Some fields are missing the space after the 'comma'
>
> > > > > I need to match on any 'comma + character'
> > > > > but not 'comma + space'
> > > > > and replace with 'comma + space + original character'
>
> > > > > There can be more than one error in a field
>
> > > > > so I guess I need something like
>
> > > > > Update mytable set myfield = replace (mytable.myfield, some wonderful
> > > > > regexp)
>
> > > > > Any gratefully appreciated
>
> > > > > Richard
>
> > > > Your problem is that the field violates First Normal Form (1NF).
>
> > > > Normalise your database and this problem will never ooccur.http://del.icio.us/Captain_Paralytic/normalization
>
> > > Captain,
>
> > > Thanks for the reply, but I do not know what you mean.
>
> > > It is a Text field and I want to put a space after every comma where
> > > there is not one for presentation purposes.
>
> > > Richard- Hide quoted text -
>
> > > - Show quoted text -
>
> > It looks as though your field contains multiple values (a list of
> > names). Is this not the case?
>
> > "The first normal form (or 1NF) requires that the values in each
> > column of a table are atomic. By atomic we mean that there are no sets
> > of values within a column."
>
> Captain,
>
> No - it is not the case. I just used names as an example, to show my
> problem / question. It is a text field, which has been created from
> various sources and the spacing was in error in one or two cases and I
> thought a regular expression would be the best way of fixing it as I
> had some matches and some 'not' matches in the same field / test.
>
> I can not find an example of 'sql' where a regular expression is used
> to update a field in the circumstances I have outlined.
>
> If someone can point me to an example - I would be most grateful.
>
> Richard- Hide quoted text -
>
> - Show quoted text -

Ok, now we know what teh actual situation is...
REGEXs in MySQL are used for pattern matching, not data manipulation.

You could try:
REPLACE(REPLACE(field,', ',','),',',', ')