Replace the shortest match of a string pattern

Question

I have this string:

update mytable set mycol=myvalue where mycol=yourvalue;

I need to convert it to:

insert into mytemp select * from mytable where mycol=youvalue;

I can accomplish it like this and it works just fine:

sed -e 's/^Update.*where//ig' -e "s/^/insert into mytemp select * from mytable where  /g" n.txt

BUT:

If the string is:

update mytable set mycol=myvalue where mycol=(select yourcol from yourtable where youcol=yourvalue);

I get:

insert into mytemp select * from mytable where youcol=yourvalue);

Whereas I want:

insert into mytemp select * from mytable where mycol=(select yourcol from yourtable where youcol=yourvalue);

What can I do?

Solution

By default sed's regex engine is greedy. This means that a pattern always matches the longest possible match. You must do a non-greedy search but i think sed doesn't support non-greedy searches. Therefore you must add a pivot point(s) to your search pattern so that sed finds the shortest possible match.

The following line tries to emulate non-greedy matching for your special case and it's not demanding universality, since a single w between update and the first where makes the pattern invalid:

sed -e 's/^Update[^w]*where//ig'\
    -e "s/^/insert into mytemp select * from mytable where  /g" n.txt

Other regex-engines support this feature, like for example the one of perl and awk.

But in your case i think an expression like this

sed -e 's/^Update.\+where\(.\+where.*\)$/\
insert into mytemp select * from mytable where \1/ig'  n.txt

would be more convenient related to your specific problem.

(the trailing \ in the lines above are only added to make the lines more legible.)

Comments

[abinaya]

Thanks a lot, it resolves this specific problem. But if I have multiple slightly different line like below, then this does not works: update mytable set mycol=myvalue where mycol=yourvalue; and insert into mytemp select * from mytable where mycol=youvalue;

[abinaya]

Gilles, the perl solution works just fine, thanks a lot!

[abinaya]

Hmm actually it is not working... I thought it did.

[abinaya]

Here it what it does: I have couple of updates:UPDATE abciii.cash_activity SET UPDATE_DATE = sysdate , USER_CHAR1 = TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MI:SS AM'), USER_CHAR2 = 'UXXXXXX' WHERE CASH_INT = 41166153 AND ENTITY_ID = 'NNNNNN' and intfc_inst = 14; UPDATE shhhyh.cash_activity SET UPDATE_DATE = sysdate , USER_CHAR1 = TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MI:SS AM'), USER_CHAR2 = 'UPDATESCRIPT' WHERE entity_id = (select entity_id from shhhsyh.cash_activity WHERE CASH_INT = 41166153 AND ENTITY_ID = 'NNNNN' and src_intfc_inst = 14); ~

[abinaya]

I want to take everything from first UPDATE to the first WHERE and replace it with : insert into nncknn.cash_activ_02_tmp select * from nnhfjfn.cash_activity where

[abinaya]

sed -e 's/^update[^()]*where//i' -e 's/^/insert into mytemp select [^()]* from mytable where /' is not working

[abinaya]

if there is only one WHERE in the string my own sed work, but if there are more than on WHERE in the string, it takes the last WHERE. Here is the one which works with one WHERE: sed -e 's/^Update.*where//ig' -e "s/^/insert into ${TMPNAME} select * from ${TABNAME} where /g" ${UPDATEFILE} > ${INSERTFILE}