CODEWITHSUNDEEP
bashsed
Niel de Wet
Niel de Wet

Reputation: 8398

Sed matching to the end of the line

I have a mysqldump with several lines like

CONSTRAINT `FK5E61277CBAE1E8F6` FOREIGN KEY (`action_item_group_id`) REFERENCES `action_item_group` (`id`),

or

CONSTRAINT `FK5E61277CBAE1E8F6` FOREIGN KEY (`action_item_group_id`) REFERENCES `action_item_group` (`id`)

I want to add the words ON UPDATE CASCADE to each of these, before the comma, if it exists.

In Vi I match it with: /CONSTRAINT\ .*\ FOREIGN\ KEY\ .*\ REFERENCES\ .*\ \(.*\) which gives me the exact text up to the comma if it exists, or up to the end of the line if it doesn't exist.

But now with sed I do:

sed -e "s/CONSTRAINT\ .*\ FOREIGN\ KEY\ .*\ REFERENCES\ .*\ \(.*\)/&\ ON\ UPDATE\ CASCADE/"

The problem is & also matches the comma, and I get output like

CONSTRAINT `FK5E61277C881C85E3` FOREIGN KEY (`created_by_employee_id`) REFERENCES `employee` (`id`), ON UPDATE CASCADE

How can I get my ON UPDATE CASCADE in before the comma?

Upvotes: 0

Views: 455

Answers (4)

Nico Huysamen
Nico Huysamen

Reputation: 10417

Easy with AWK:

EXAMPLE

cat filename.sql | awk '{ cnstr = match($0, "CONSTRAINT"); if (cnstr != 0) { idx = match($0,"\,[^\,]*$"); if (idx == 0) { print $0 " ON UPDATE CASCADE" } else { print substr($0,0,idx-1) " ON UPDATE CASCADE," } } else { print $0 } }' > output.sql

NB

The CONSTRAINT in match can be replaced with whatever regex you want to match. This is just an easy example. It should work though.

Upvotes: 2

OpenSauce
OpenSauce

Reputation: 8623

"extended" regular expressions (-r flag with sed) always make more sense to me. Then you get \( meaning a literal parenthesis and ( meaning start of a group, which is what you seem to be expecting (as per Diego Sevilla). This works for me:

echo 'CONSTRAINT `FK5E61277CBAE1E8F6` FOREIGN KEY (`action_item_group_id`) REFERENCES `action_item_group` (`id`),' | sed -r "s/CONSTRAINT\ .*\ FOREIGN\ KEY\ .*\ REFERENCES\ .*\ \(.*\)/&\ ON\ UPDATE\ CASCADE/"

result:

CONSTRAINT `FK5E61277CBAE1E8F6` FOREIGN KEY (`action_item_group_id`) REFERENCES `action_item_group` (`id`) ON UPDATE CASCADE,

Upvotes: 2

Fredrik Pihl
Fredrik Pihl

Reputation: 45662

Can't you just replace the , with ON UPDATE CASCADE, or did I misunderstand the question?

$ echo "some string ending with ," | sed 's/,/NEW,/'
some string ending with NEW,

Upvotes: 0

Diego Sevilla
Diego Sevilla

Reputation: 29021

Try removing the \ before the parentheses in the substitute expression:

sed -e "s/CONSTRAINT\ .*\ FOREIGN\ KEY\ .*\ REFERENCES\ .*\ (.*)/&\ ON\ UPDATE\ CASCADE/"

The \( means a start of a group, not a parenthesis. ( just matches the left paren.

Upvotes: 2

Related Questions