Replacing ONLY the first occurence of a substring in a string

I have a field which has multiple comma characters in it and I want to
change the first occurrence only.

I can not work out how to do this as replace changes all occurrences.

eg

Field
'fred, bob, june, 12/34/56'
needs to become
'fred^ bob, june, 12/34/56'

where the first comma ',' becomes a circumflex '^'

The first comma can be in any position or not present at all

I imagine it will be something like

set field1= IF(POS(field1,','), CONCAT(substr field1 up to comma, '^',
substr field1 after comma),else field1 Unchanged)

If someone could help before I wipe my whole field I would be most
grateful.

Richard

"mcl" <mcl.office@googlemail.com> schreef in bericht
news:dd05f666-41df-4c01-8473-7fb91e9a7634@z17g2000hsg.googlegroups.com...
>I have a field which has multiple comma characters in it and I want to
> change the first occurrence only.
>
> I can not work out how to do this as replace changes all occurrences.
>
> eg
>
> Field
> 'fred, bob, june, 12/34/56'
> needs to become
> 'fred^ bob, june, 12/34/56'
>
> where the first comma ',' becomes a circumflex '^'
>
> The first comma can be in any position or not present at all
>
> I imagine it will be something like
>
> set field1= IF(POS(field1,','), CONCAT(substr field1 up to comma, '^',
> substr field1 after comma),else field1 Unchanged)
>
> If someone could help before I wipe my whole field I would be most
> grateful.
>
> Richard


set field1= REPLACE(field1,LEFT(field1,LOCATE(',',field1)),
CONCAT(LEFT(field1,LOCATE(',',field1)-1),'^'))

of course with the check added that there should be a ',' in your `field1`


--
Luuk

On Jan 16, 12:51 pm, "Luuk" <l...@invalid.lan> wrote:
> "mcl" <mcl.off...@googlemail.com> schreef in berichtnews:dd05f666-41df-4c01-8473-7fb91e9a7634@z17g2000hsg.googlegroups.com...
>
>
>
> >I have a field which has multiple comma characters in it and I want to
> > change the first occurrence only.
>
> > I can not work out how to do this as replace changes all occurrences.
>
> > eg
>
> > Field
> > 'fred, bob, june, 12/34/56'
> > needs to become
> > 'fred^ bob, june, 12/34/56'
>
> > where the first comma ',' becomes a circumflex '^'
>
> > The first comma can be in any position or not present at all
>
> > I imagine it will be something like
>
> > set field1= IF(POS(field1,','), CONCAT(substr field1 up to comma, '^',
> > substr field1 after comma),else field1 Unchanged)
>
> > If someone could help before I wipe my whole field I would be most
> > grateful.
>
> > Richard
>
> set field1= REPLACE(field1,LEFT(field1,LOCATE(',',field1)),
> CONCAT(LEFT(field1,LOCATE(',',field1)-1),'^'))
>
> of course with the check added that there should be a ',' in your `field1`
>
> --
> Luuk

Many thanks - worked a treat, but it took me a while to work out what
was involved

I guess the old grey matter is just not what it was.

Richard