CODEWITHSUNDEEP
macosterminalgrep
Paul
Paul

Reputation: 2515

Remove line and previous comma from sql file using GREP

I am trying to turn this: 

  KEY `vehicle_classification_id` (`vehicle_classification_id`),
  CONSTRAINT `BodyStyle_VehicleClassification` FOREIGN KEY 
(`vehicle_classification_id`) REFERENCES `vehicle_classifications` (`id`$
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

into this:

  KEY `vehicle_classification_id` (`vehicle_classification_id`)
(`vehicle_classification_id`) REFERENCES `vehicle_classifications` (`id`$
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

THIS SED AND GREP COMMAND REMOVE THE 'CONTRAINT' LINE, JUST NEED TO REMOVE THE COMMA FROM THE PREVIOUS LINE:

grep -vE ",*CONSTRAINT*" ~/Test.sql > ~/Stripped.sql
sed  -e 's/,*CONSTRAINT.*//' ~/Test.sql

If anyone knows how to do this with sed I would appreciate some guidance. Thank you.

Upvotes: 0

Views: 75

Answers (2)

tink
tink

Reputation: 15204

I wouldn't get hung-up on the multi-line part (assuming that you have several similar blocks, you're not giving much sample data to work with):

echo '  KEY `vehicle_classification_id` (`vehicle_classification_id`),
  CONSTRAINT `BodyStyle_VehicleClassification` FOREIGN KEY 
(`vehicle_classification_id`) REFERENCES `vehicle_classifications` (`id`$
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;' | tr '\n' '\f' | sed  -e 's/,\f *CONSTRAINT[^\f]*\f/\f/'  | tr '\f' '\n'
  KEY `vehicle_classification_id` (`vehicle_classification_id`)
(`vehicle_classification_id`) REFERENCES `vehicle_classifications` (`id`$
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

Upvotes: 2

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

sed approach:

sed '/\s*KEY /{ N; s/,\s*CONSTRAINT .*//}' ~/Test.sql > ~/Stripped.sql

Upvotes: 2

Related Questions