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

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

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

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."

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

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,', ',','),',',', ')

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

On 26 Sep, 16:17, mcl <mcl.off...@googlemail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

In most languages functions can be nested.