On 26 Sep, 15:48, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> 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,', ',','),',',', ')

Captain,

Perfect - make them all comma and then replace with comma space. I
was not aware of nesting as you have shown. It worked.

REGEXP - more reading but noted for pattern matching only. I was
probably thinking about 'sed' a program I once used many moons ago.

Thanks again

Richard