Sunday, March 13, 2022

[SOLVED] Remove comma character in multi line expression using SED

Issue

I have a text file like this:

CREATE TABLE `table_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_attribute1` int(11) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `fk_user_idx` (`user_id`,`user_attribute1`),
  KEY `fk_user_attribute1_idx` (`user_attribute1`),
) ENGINE=InnoDB;


CREATE TABLE `table_product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_attribute1` int(11) NOT NULL,
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `fk_product_idx` (`product_id`,`product_attribute1`),
  KEY `fk_product_attribute1_idx` (`product_attribute1`),


) ENGINE=InnoDB;

CREATE TABLE `table_ads` (
  `ad_id` int(11) NOT NULL AUTO_INCREMENT,
  `ad_attribute1` int(11) NOT NULL,
  PRIMARY KEY (`ad_id`),
  UNIQUE KEY `fk_ad_idx` (`ad_id`,`ad_attribute1`),
  KEY `fk_ad_attribute1_idx` (`ad_attribute1`),





) ENGINE=InnoDB;

As you will notice before the closing parenthesis of each "Create table", there is a line that ends with a comma and a variable number of new lines.

In Bash with sed command i want to remove the last comma character to create a valid SQL.

I try some expressions like this

sed 's/,[[:space:]]*)//'

but it didn't works, probably i need to make a multiline search but i don't know how to do this.

How to achieve this?


Solution

Using gnu-sed you can do this using -z option:

sed -zE 's/,\n*(\n\) ENGINE)/\1/g' file.db

CREATE TABLE `table_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_attribute1` int(11) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `fk_user_idx` (`user_id`,`user_attribute1`),
  KEY `fk_user_attribute1_idx` (`user_attribute1`)
) ENGINE=InnoDB;


CREATE TABLE `table_product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_attribute1` int(11) NOT NULL,
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `fk_product_idx` (`product_id`,`product_attribute1`),
  KEY `fk_product_attribute1_idx` (`product_attribute1`)
) ENGINE=InnoDB;

CREATE TABLE `table_ads` (
  `ad_id` int(11) NOT NULL AUTO_INCREMENT,
  `ad_attribute1` int(11) NOT NULL,
  PRIMARY KEY (`ad_id`),
  UNIQUE KEY `fk_ad_idx` (`ad_id`,`ad_attribute1`),
  KEY `fk_ad_attribute1_idx` (`ad_attribute1`)
) ENGINE=InnoDB;


Answered By - anubhava
Answer Checked By - Clifford M. (WPSolving Volunteer)
at