How to regex replace part of a string?

Hi Forum

How to replace the beginings- and the ends of string without any change of the string in between?

An example:

this is my input:

col1
H1, verylongstring1... ,H
H2, verylongstring2... ,H

and this should be my output after replace:

col1
P1, verylongstring1... ,P11
P2, verylongstring2... ,P22
.

So far i can select col1 by:

SELECT col1 FROM table1 WHERE col1 REGEXP 'H1.*H';

but in :
UPDATE table1 SET col1 = REPLACE( col1, REGEXP 'H1.*H', REGEXP'P1.*P11');

the REGEXP does not work !?

thanks a lot for your help

You cannot combine regexp and replace() since matched parts cannot be captured.
http://forums.mysql.com/read.php?20,54128,54199

is that pseudo-html you're storing there?

yes, you could say that.
actually the beginning and the ends of the strings are some tags that i want to search and replace , while i keep the inside content intact.

SUBSTRING_INDEX with 1 and then -1 will allow you to find the first and last bits

if you only have the tags at the front and back, why do you store them at all?

Thank you,
What do you mean by 'why do you store them at all?' Are there any smarter ways to search and replace tags in the beginning and the end of a string?

SUBSTRING_INDEX will probably work in combination with REPLACE to do my search and replace. I was not aware of it. But I have not tried it yet.

Meanwhile I managed to do the job with two UPDATE queries.
The first one finds 'H1' and replaces it with 'P1'
and the second one finds 'H' and replaces it with 'P11' if in the beginning there is a 'P1'.

UPDATE table1 SET = col1 REPLACE( col1, 'H1', 'P1') WHERE col1 REGEXP 'H1.*H';
UPDATE table1 SET = col1 REPLACE( col1, 'H', 'P11') WHERE col1 REGEXP 'P1.*P11';

It could be nice if I could merge those two UPDATE queries into one and still do the same job !

have a close look at what you are actually saying here --

... REPLACE( col1, 'H', 'P11')

i hope you made a backup

Until mysql starts to run as a normal database out of the box (in this case I mean with autocommit set to OFF)

and how would autocommit help someone get back all the Hs that were replaced other than the ones he actually meant to change?

before: H1,i hope you made a backup,H

after: P11,i P11ope you made a backup,P11

Rollback?

and if you're on a hosted shared server?

I don't see the difference.
I mean that you should be doing this inside a transaction which could be committed or rolled back after checking the results.
Every (other) database works this way, but I think that mysql allows to set autocommit off for your session even if others are running with autocommit on (not shure).


Also, a restore on a hosted shared server would be more difficult than doing the same on your own private server.

dude, you might know that, but people new to databases don't

remember, we are talking almost always to web developers who are not seasoned DBAs

Q: i lost my data! halp!
A: did you take a backup?
Q: no
A: that's bad judgment
Q: oh? and where does good judgment come from?
A: experience
Q: and where does experience come from?
A: bad judgment

your interesting points makes me curious!

Any of you guys can maybe provide some hints about
how to do update / delete inside a transaction?

- to check the result before commit of course

See my blog