Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

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

share|improve this answer
    
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; –  Nirmal Arri Sep 19 '12 at 9:16
add comment

Regular expression matching is performed from left to right, and with the longest match taken in preference. Hence ^Update.*where matches the last occurrence of where on the line.

One way to do this matching would be to use a non-greedy quantifier for *. Sed doesn't support non-greedy quantifiers, but perl does.

perl -pe 's/^update.*?where//i; s/^/insert into mytemp select .*? from mytable where /'

Another way which may or may not match your data would be to reject parentheses in the table name and colum settings.

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

A more elaborate method would be to first replace the first where by a unique marker, then make your replacement, and finally turn back the marker into where. Since sed operates line by line, a line is guaranteed not to contain a newline character, represented by \n in sed.

sed -e 's/ where /\n/' \
    -e 's/^update.*$//i' -e 's/^/insert into mytemp select .* from mytable where /' \
    -e 's/\n/ where/'
share|improve this answer
    
Gilles, the perl solution works just fine, thanks a lot! –  Nirmal Arri Sep 19 '12 at 9:22
    
Gilles This is great took away all my pain! Thanks! –  Nirmal Arri Sep 20 '12 at 19:34
    
Hmm actually it is not working... I thought it did. –  Nirmal Arri Oct 4 '12 at 19:04
    
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); ~ –  Nirmal Arri Oct 4 '12 at 19:08
    
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 –  Nirmal Arri Oct 4 '12 at 19:11
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.